-- VisualGen AI MySQL Schema (MySQL 5.7+ / 8.0+)
-- Engine: InnoDB
-- Charset: utf8mb4

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `rate_limits`;
DROP TABLE IF EXISTS `reports`;
DROP TABLE IF EXISTS `credit_transactions`;
DROP TABLE IF EXISTS `subscriptions`;
DROP TABLE IF EXISTS `plans`;
DROP TABLE IF EXISTS `favorites`;
DROP TABLE IF EXISTS `edited_images`;
DROP TABLE IF EXISTS `generations`;
DROP TABLE IF EXISTS `providers`;
DROP TABLE IF EXISTS `templates`;
DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `users`;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Users Table
CREATE TABLE `users` (
    `id` VARCHAR(36) NOT NULL,
    `firebase_uid` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `avatar_url` VARCHAR(1024) DEFAULT NULL,
    `token_hash` VARCHAR(64) DEFAULT NULL, -- SHA-256 hash of API token
    `credits` INT NOT NULL DEFAULT 10,
    `plan_id` VARCHAR(36) DEFAULT NULL,
    `plan_expires_at` DATETIME DEFAULT NULL,
    `fcm_token` VARCHAR(255) DEFAULT NULL,
    `is_banned` TINYINT(1) NOT NULL DEFAULT 0,
    `banned_reason` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_login_at` DATETIME DEFAULT NULL,
    `deleted_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_firebase_uid` (`firebase_uid`),
    KEY `idx_token_hash` (`token_hash`),
    KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Categories Table
CREATE TABLE `categories` (
    `id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `icon` VARCHAR(100) NOT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `idx_sort_active` (`sort_order`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Templates Table
CREATE TABLE `templates` (
    `id` VARCHAR(36) NOT NULL,
    `category_id` VARCHAR(36) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `preview_image` VARCHAR(512) NOT NULL, -- Relative path to public preview
    `sample_outputs` JSON DEFAULT NULL, -- Array of strings (urls or paths)
    `prompt_text` TEXT NOT NULL,
    `negative_prompt` TEXT DEFAULT NULL,
    `is_premium` TINYINT(1) NOT NULL DEFAULT 0,
    `is_trending` TINYINT(1) NOT NULL DEFAULT 0,
    `credit_cost` INT NOT NULL DEFAULT 1,
    `sort_order` INT NOT NULL DEFAULT 0,
    `use_count` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_templates_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `idx_active_sort` (`is_active`, `sort_order`),
    KEY `idx_category_active` (`category_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Providers Table
CREATE TABLE `providers` (
    `id` VARCHAR(36) NOT NULL,
    `name` ENUM('gemini', 'openai') NOT NULL,
    `model_name` VARCHAR(100) NOT NULL,
    `api_key_encrypted` TEXT NOT NULL, -- Encrypted AES-256-GCM API key
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `priority` INT NOT NULL DEFAULT 1, -- Lower values = higher priority
    `daily_limit` INT NOT NULL DEFAULT 500,
    `used_today` INT NOT NULL DEFAULT 0,
    `last_error` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_priority_active` (`is_active`, `priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Generations Table
CREATE TABLE `generations` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NOT NULL,
    `template_id` VARCHAR(36) NOT NULL,
    `provider_id` VARCHAR(36) DEFAULT NULL,
    `idempotency_key` VARCHAR(64) NOT NULL,
    `input_path` VARCHAR(512) NOT NULL, -- Path relative to private upload storage
    `output_path` VARCHAR(512) DEFAULT NULL, -- Path relative to private generated storage
    `user_prompt` TEXT DEFAULT NULL,
    `final_prompt` TEXT NOT NULL,
    `strength` INT NOT NULL DEFAULT 100,
    `status` ENUM('pending', 'success', 'failed', 'blocked') NOT NULL DEFAULT 'pending',
    `credits_used` INT NOT NULL DEFAULT 0,
    `error_message` TEXT DEFAULT NULL,
    `duration_ms` INT NOT NULL DEFAULT 0,
    `is_hidden` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_idempotency_key` (`idempotency_key`),
    CONSTRAINT `fk_generations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_generations_template` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_generations_provider` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON UPDATE CASCADE,
    KEY `idx_user_status` (`user_id`, `status`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Edited Images Table
CREATE TABLE `edited_images` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NOT NULL,
    `generation_id` VARCHAR(36) DEFAULT NULL,
    `source_path` VARCHAR(512) NOT NULL,
    `edited_path` VARCHAR(512) NOT NULL,
    `edit_meta` JSON DEFAULT NULL, -- Metadata of modifications (rotation, watermark parameters)
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_edited_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_edited_generation` FOREIGN KEY (`generation_id`) REFERENCES `generations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    KEY `idx_user_edited` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Favorites Table
CREATE TABLE `favorites` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NOT NULL,
    `generation_id` VARCHAR(36) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_generation` (`user_id`, `generation_id`),
    CONSTRAINT `fk_favorites_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_favorites_generation` FOREIGN KEY (`generation_id`) REFERENCES `generations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Plans Table
CREATE TABLE `plans` (
    `id` VARCHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `google_product_id` VARCHAR(255) NOT NULL,
    `credits_per_period` INT NOT NULL,
    `watermark_free` TINYINT(1) NOT NULL DEFAULT 1,
    `period_days` INT NOT NULL DEFAULT 30,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_google_product_id` (`google_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Subscriptions Table
CREATE TABLE `subscriptions` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NOT NULL,
    `plan_id` VARCHAR(36) NOT NULL,
    `purchase_token` VARCHAR(255) NOT NULL,
    `order_id` VARCHAR(255) DEFAULT NULL,
    `status` VARCHAR(50) NOT NULL, -- e.g., active, expired, canceled, on_hold
    `starts_at` DATETIME NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `auto_renewing` TINYINT(1) NOT NULL DEFAULT 0,
    `last_verified_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_purchase_token` (`purchase_token`),
    CONSTRAINT `fk_subscriptions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_subscriptions_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `idx_expires_status` (`expires_at`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Credit Transactions Table
CREATE TABLE `credit_transactions` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NOT NULL,
    `amount` INT NOT NULL, -- Positive for addition, Negative for deduction
    `reason` VARCHAR(100) NOT NULL, -- e.g., google_signup, daily_refill, purchase, generation_deduct, generation_refund
    `ref_id` VARCHAR(36) DEFAULT NULL, -- e.g., subscription_id, generation_id
    `balance_after` INT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `idx_user_created` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Reports Table
CREATE TABLE `reports` (
    `id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NOT NULL,
    `generation_id` VARCHAR(36) NOT NULL,
    `reason` VARCHAR(100) NOT NULL, -- sexual, violence, hate, etc.
    `note` TEXT DEFAULT NULL,
    `status` ENUM('open', 'reviewed', 'actioned') NOT NULL DEFAULT 'open',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_reports_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_reports_generation` FOREIGN KEY (`generation_id`) REFERENCES `generations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Rate Limits Table (Sliding Window / Key-Value representation)
CREATE TABLE `rate_limits` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `key_hash` VARCHAR(64) NOT NULL, -- sha256 of IP or token
    `window_start` BIGINT UNSIGNED NOT NULL, -- UNIX timestamp in seconds
    `hits` INT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_key_window` (`key_hash`, `window_start`),
    KEY `idx_window_start` (`window_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13. Settings Table (Key-Value Configuration Storage)
CREATE TABLE `settings` (
    `skey` VARCHAR(50) NOT NULL,
    `svalue` TEXT DEFAULT NULL,
    PRIMARY KEY (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update users foreign key constraint for clean plan association
ALTER TABLE `users` ADD CONSTRAINT `fk_users_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;