SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS series;
DROP TABLE IF EXISTS tag;
DROP TABLE IF EXISTS asset_pack;
DROP TABLE IF EXISTS asset_pack_image;
DROP TABLE IF EXISTS asset_pack_tag;
DROP TABLE IF EXISTS update_log;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE category (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE series (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE tag (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE asset_pack (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL UNIQUE,
  category_id INT NOT NULL,
  series_id INT,
  discovered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  released_at DATE,
  updated_at DATE,
  base_asset_path VARCHAR(255),
  orig_page_link VARCHAR(255),
  FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (series_id) REFERENCES series(id) ON DELETE SET NULL ON UPDATE CASCADE,
  INDEX idx_name (name),
  INDEX idx_category (category_id),
  INDEX idx_series (series_id)
);

CREATE TABLE asset_pack_image (
  id INT PRIMARY KEY AUTO_INCREMENT,
  asset_pack_id INT NOT NULL,
  image_file_name VARCHAR(255),
  orig_file_link VARCHAR(255),
  FOREIGN KEY (asset_pack_id) REFERENCES asset_pack(id) ON DELETE CASCADE,
  INDEX idx_asset_pack_id (asset_pack_id)
);

CREATE TABLE asset_pack_tag (
  asset_pack_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (asset_pack_id, tag_id),
  FOREIGN KEY (asset_pack_id) REFERENCES asset_pack(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE
);

CREATE TABLE update_log (
  id INT PRIMARY KEY AUTO_INCREMENT,
  asset_pack_id INT NOT NULL,
  released_date DATE NOT NULL,
  version VARCHAR(20),
  description VARCHAR(500),
  files_count INT UNSIGNED DEFAULT 0,
  feat_animations TINYINT(1) DEFAULT 0,
  feat_variations TINYINT(1) DEFAULT 0,
  zip_file_name VARCHAR(255),
  orig_download_link VARCHAR(255),
  FOREIGN KEY (asset_pack_id) REFERENCES asset_pack(id) ON DELETE CASCADE,
  INDEX idx_released_date (released_date)
);