-- Initial database schema for CATEOR

CREATE TABLE cat_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    bio VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cat_collections (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    theme VARCHAR(100) NULL,
    cover_image VARCHAR(255) NULL,
    is_public TINYINT(1) DEFAULT 0,
    share_token VARCHAR(64) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_cat_collections_user FOREIGN KEY (user_id) REFERENCES cat_users(id) ON DELETE CASCADE,
    INDEX idx_cat_collections_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cat_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    collection_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    item_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cat_items_collection FOREIGN KEY (collection_id) REFERENCES cat_collections(id) ON DELETE CASCADE,
    INDEX idx_cat_items_collection (collection_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cat_item_photos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cat_item_photos_item FOREIGN KEY (item_id) REFERENCES cat_items(id) ON DELETE CASCADE,
    INDEX idx_cat_item_photos_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cat_likes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    collection_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cat_likes_user FOREIGN KEY (user_id) REFERENCES cat_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_cat_likes_collection FOREIGN KEY (collection_id) REFERENCES cat_collections(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_cat_like (user_id, collection_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cat_follows (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    target_user_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cat_follows_user FOREIGN KEY (user_id) REFERENCES cat_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_cat_follows_target FOREIGN KEY (target_user_id) REFERENCES cat_users(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_cat_follow (user_id, target_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
