








DROP TABLE IF EXISTS album_categories;
DROP TABLE IF EXISTS album_tags;
DROP TABLE IF EXISTS album_performers;
DROP TABLE IF EXISTS album_channels;


CREATE TABLE IF NOT EXISTS album_categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id INT UNSIGNED DEFAULT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    description TEXT DEFAULT NULL,
    thumbnail VARCHAR(255) DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    album_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    video_type ENUM('hetero','gay','hentai') NOT NULL DEFAULT 'hetero',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY slug_type (slug, video_type),
    KEY idx_parent (parent_id),
    KEY idx_sort (sort_order),
    KEY idx_acat_type (video_type),
    KEY idx_acat_count (album_count, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_tags (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(60) NOT NULL,
    video_type ENUM('hetero','gay','hentai') NOT NULL DEFAULT 'hetero',
    album_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY slug_type (slug, video_type),
    FULLTEXT KEY ft_name (name),
    KEY idx_atag_type (video_type),
    KEY idx_atag_count (album_count, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_performers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    bio TEXT DEFAULT NULL,
    photo VARCHAR(255) DEFAULT NULL,
    country VARCHAR(2) DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    social_links LONGTEXT DEFAULT NULL,
    video_type ENUM('hetero','gay','hentai') NOT NULL DEFAULT 'hetero',
    album_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY slug_type (slug, video_type),
    FULLTEXT KEY ft_name (name),
    KEY idx_country (country),
    KEY idx_aperf_type (video_type),
    KEY idx_aperf_count (album_count, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_channels (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    description TEXT DEFAULT NULL,
    logo VARCHAR(500) DEFAULT NULL,
    website VARCHAR(500) DEFAULT NULL,
    country VARCHAR(100) DEFAULT NULL,
    video_type ENUM('hetero','gay','hentai') NOT NULL DEFAULT 'hetero',
    album_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY slug_type (slug, video_type),
    FULLTEXT KEY ft_name (name),
    KEY idx_achan_type (video_type),
    KEY idx_achan_count (album_count, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS album_category_map (
    album_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (album_id, category_id),
    KEY idx_category_album (category_id, album_id),
    CONSTRAINT FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (category_id) REFERENCES album_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_tag_map (
    album_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (album_id, tag_id),
    KEY idx_tag_album (tag_id, album_id),
    CONSTRAINT FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (tag_id) REFERENCES album_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_performer_map (
    album_id INT UNSIGNED NOT NULL,
    performer_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (album_id, performer_id),
    KEY idx_performer_album (performer_id, album_id),
    CONSTRAINT FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (performer_id) REFERENCES album_performers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_channel_map (
    album_id INT UNSIGNED NOT NULL,
    channel_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (album_id, channel_id),
    KEY idx_channel_album (channel_id, album_id),
    CONSTRAINT FOREIGN KEY (album_id) REFERENCES albums(id) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (channel_id) REFERENCES album_channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS album_category_translations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED NOT NULL,
    lang_code VARCHAR(5) NOT NULL,
    name VARCHAR(200) DEFAULT NULL,
    slug VARCHAR(220) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY idx_acat_lang (category_id, lang_code),
    KEY idx_lang_slug (lang_code, slug),
    CONSTRAINT FOREIGN KEY (category_id) REFERENCES album_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_tag_translations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag_id INT UNSIGNED NOT NULL,
    lang_code VARCHAR(5) NOT NULL,
    name VARCHAR(200) DEFAULT NULL,
    slug VARCHAR(220) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY idx_atag_lang (tag_id, lang_code),
    KEY idx_lang_slug (lang_code, slug),
    CONSTRAINT FOREIGN KEY (tag_id) REFERENCES album_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_performer_translations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    performer_id INT UNSIGNED NOT NULL,
    lang_code VARCHAR(5) NOT NULL,
    name VARCHAR(200) DEFAULT NULL,
    slug VARCHAR(220) DEFAULT NULL,
    bio TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY idx_aperf_lang (performer_id, lang_code),
    KEY idx_lang_slug (lang_code, slug),
    CONSTRAINT FOREIGN KEY (performer_id) REFERENCES album_performers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS album_channel_translations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_id INT UNSIGNED NOT NULL,
    lang_code VARCHAR(5) NOT NULL,
    name VARCHAR(200) DEFAULT NULL,
    slug VARCHAR(220) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY idx_achan_lang (channel_id, lang_code),
    KEY idx_lang_slug (lang_code, slug),
    CONSTRAINT FOREIGN KEY (channel_id) REFERENCES album_channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
