-- MySQL Schema for iTools NexusAI Image Generation Studio Admin System
CREATE DATABASE IF NOT EXISTS `nexusai_studio` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `nexusai_studio`;

-- Table 1: Art Styles
CREATE TABLE IF NOT EXISTS `styles` (
  `id` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `prompt` text NOT NULL,
  `category` varchar(100) DEFAULT 'General',
  `image` text DEFAULT NULL,
  `display_mode` varchar(50) DEFAULT 'thumbnail',
  `is_featured` tinyint(1) DEFAULT 1,
  `created_at` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_featured` (`is_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 2: Keycodes & Credit Tracking
CREATE TABLE IF NOT EXISTS `keycodes` (
  `keycode` varchar(64) NOT NULL,
  `credit_limit` int(11) DEFAULT 1000,
  `used_credits` int(11) DEFAULT 0,
  `status` enum('active','disabled','expired') DEFAULT 'active',
  `created_at` int(11) NOT NULL,
  `last_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`keycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 3: API Call Logs
CREATE TABLE IF NOT EXISTS `api_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` int(11) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `tool` varchar(64) NOT NULL,
  `keycode` varchar(64) DEFAULT NULL,
  `status_code` int(11) NOT NULL,
  `error_message` text DEFAULT NULL,
  `credits_cost` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_keycode` (`keycode`),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 4: Prompt Library
CREATE TABLE IF NOT EXISTS `prompt_library` (
  `id` varchar(64) NOT NULL,
  `title` varchar(255) NOT NULL,
  `prompt` text NOT NULL,
  `category` varchar(100) DEFAULT 'General',
  `tags` varchar(255) DEFAULT '',
  `is_preset` tinyint(1) DEFAULT 0,
  `created_at` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table 5: Prompt Builder Presets
CREATE TABLE IF NOT EXISTS `prompt_builder_presets` (
  `id` varchar(64) NOT NULL,
  `category` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` text NOT NULL,
  `icon` varchar(100) DEFAULT 'fa-wand-magic-sparkles',
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Sample Demo Keycode
INSERT INTO `keycodes` (`keycode`, `credit_limit`, `used_credits`, `status`, `created_at`, `last_active`)
VALUES ('6854-4285-3148-1648', 1000, 25, 'active', UNIX_TIMESTAMP(), UNIX_TIMESTAMP())
ON DUPLICATE KEY UPDATE `last_active` = VALUES(`last_active`);
