-- SQL migration adapted from Django models (MySQL dialect)
CREATE TABLE `catalog_app_catalog` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` longtext,
  `created_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `catalog_app_catalog_slug_uniq` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_category` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `catalog_app_category_name_uniq` (`name`),
  UNIQUE KEY `catalog_app_category_slug_uniq` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_author` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `bio` longtext,
  `created_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_rightstype` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `catalog_app_rightstype_code_uniq` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_book` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `catalog_id` bigint NOT NULL,
  `category_id` bigint DEFAULT NULL,
  `author_id` bigint DEFAULT NULL,
  `title` varchar(512) NOT NULL,
  `subtitle` varchar(512) DEFAULT NULL,
  `description` longtext,
  `cover_url` varchar(1024) DEFAULT NULL,
  `publication_year` int DEFAULT NULL,
  `isbn` varchar(50) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catalog_id_idx` (`catalog_id`),
  KEY `category_id_idx` (`category_id`),
  KEY `author_id_idx` (`author_id`),
  CONSTRAINT `catalog_app_book_catalog_id_fk` FOREIGN KEY (`catalog_id`) REFERENCES `catalog_app_catalog` (`id`) ON DELETE CASCADE,
  CONSTRAINT `catalog_app_book_category_id_fk` FOREIGN KEY (`category_id`) REFERENCES `catalog_app_category` (`id`) ON DELETE SET NULL,
  CONSTRAINT `catalog_app_book_author_id_fk` FOREIGN KEY (`author_id`) REFERENCES `catalog_app_author` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_bookright` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `book_id` bigint NOT NULL,
  `rights_type_id` bigint NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `exclusive` tinyint(1) NOT NULL DEFAULT 0,
  `available_quantity` int DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'AVAILABLE',
  `additional_details` longtext,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `catalog_app_bookright_book_rightstype_uniq` (`book_id`,`rights_type_id`),
  KEY `book_id_idx` (`book_id`),
  KEY `rights_type_id_idx` (`rights_type_id`),
  CONSTRAINT `catalog_app_bookright_book_id_fk` FOREIGN KEY (`book_id`) REFERENCES `catalog_app_book` (`id`) ON DELETE CASCADE,
  CONSTRAINT `catalog_app_bookright_rights_type_id_fk` FOREIGN KEY (`rights_type_id`) REFERENCES `catalog_app_rightstype` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_cartitem` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `book_right_id` bigint NOT NULL,
  `quantity` int NOT NULL DEFAULT 1,
  `added_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `catalog_app_cartitem_user_bookright_uniq` (`user_id`,`book_right_id`),
  KEY `cartitem_bookright_idx` (`book_right_id`),
  CONSTRAINT `catalog_app_cartitem_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `catalog_app_cartitem_book_right_id_fk` FOREIGN KEY (`book_right_id`) REFERENCES `catalog_app_bookright` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_user_idx` (`user_id`),
  CONSTRAINT `catalog_app_order_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_orderitem` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_id` bigint NOT NULL,
  `book_right_id` bigint NOT NULL,
  `unit_price` decimal(12,2) NOT NULL,
  `quantity` int NOT NULL,
  `total_price` decimal(12,2) NOT NULL,
  `rights_snapshot` json NOT NULL,
  `created_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `orderitem_order_idx` (`order_id`),
  KEY `orderitem_bookright_idx` (`book_right_id`),
  CONSTRAINT `catalog_app_orderitem_order_id_fk` FOREIGN KEY (`order_id`) REFERENCES `catalog_app_order` (`id`) ON DELETE CASCADE,
  CONSTRAINT `catalog_app_orderitem_book_right_id_fk` FOREIGN KEY (`book_right_id`) REFERENCES `catalog_app_bookright` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `catalog_app_payment` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_id` bigint NOT NULL,
  `provider` varchar(100) DEFAULT NULL,
  `provider_payment_id` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `status` varchar(20) NOT NULL DEFAULT 'INITIATED',
  `metadata` json DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_order_idx` (`order_id`),
  CONSTRAINT `catalog_app_payment_order_id_fk` FOREIGN KEY (`order_id`) REFERENCES `catalog_app_order` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
