from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime

db = SQLAlchemy()

# 用户与分类的关联表(用户收藏的公共分类)
user_categories = db.Table('user_categories',
                           db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
                           db.Column('category_id', db.Integer, db.ForeignKey('category.id'))
                           )

# 用户与站点的关联表(用户收藏的公共站点)
user_sites = db.Table('user_sites',
                      db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
                      db.Column('site_id', db.Integer, db.ForeignKey('site.id'))
                      )


class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(200), nullable=False)
    is_admin = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # 用户自己的分类和站点
    categories = db.relationship('Category', backref='owner', lazy=True,
                                 foreign_keys='Category.user_id')
    sites = db.relationship('Site', backref='owner', lazy=True,
                            foreign_keys='Site.user_id')

    # 用户收藏的公共分类和站点
    favorite_categories = db.relationship('Category', secondary=user_categories,
                                          backref=db.backref('favorited_by', lazy='dynamic'))
    favorite_sites = db.relationship('Site', secondary=user_sites,
                                     backref=db.backref('favorited_by', lazy='dynamic'))

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return f'<User {self.username}>'


class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    is_public = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # 关系
    sites = db.relationship('Site', backref='category', lazy=True, cascade="all, delete-orphan")

    def __repr__(self):
        return f'<Category {self.name}>'


class Site(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    url = db.Column(db.String(500), nullable=False)
    description = db.Column(db.Text)
    icon = db.Column(db.String(500))  # 自动获取的favicon URL
    custom_icon = db.Column(db.String(500))  # 用户上传的自定义图标
    is_public = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    last_visited = db.Column(db.DateTime)

    # 外键
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)

    def __repr__(self):
        return f'<Site {self.name}>'