-- Gold Shop Software – Full DB Schema (Stage 2)
-- MySQL 5.7+ (5.7.8+ for JSON type; fallback: use TEXT and store JSON string).
-- Money amounts: BIGINT in smallest currency unit (e.g. Rials). Weights: DECIMAL(10,3) grams.
-- InnoDB, UTF8MB4. Run once per instance. Set superuser password via PHP if needed.


-- ----------------------------
-- users
-- ----------------------------
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mobile` varchar(20) NOT NULL COMMENT 'Primary identifier, e.g. 09120000000',
  `password_hash` varchar(255) NOT NULL,
  `role` enum('user','admin','super') NOT NULL DEFAULT 'user',
  `active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 = blocked, invalidate sessions',
  `permissions` text COMMENT 'Store as JSON string. For admin: e.g. {"products":true,"orders_gold":true}',
  `full_name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `national_id` varchar(20) DEFAULT NULL,
  `mobile_verified_at` datetime DEFAULT NULL,
  `last_login_at` datetime 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_users_mobile` (`mobile`),
  KEY `idx_users_role` (`role`),
  KEY `idx_users_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- categories (hierarchical)
-- ----------------------------
CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `sort` int(11) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_categories_parent_id` (`parent_id`),
  KEY `idx_categories_slug` (`slug`),
  KEY `idx_categories_active_sort` (`active`,`sort`),
  CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- product_types (dynamic fields: JSON or TEXT storing JSON)
-- ----------------------------
CREATE TABLE `product_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `field_definitions` text COMMENT 'Store as JSON string. e.g. {"weight":"number","image":"file","description":"text"}',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `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_product_types_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- products (weight-based or dollar-based pricing)
-- Weight formula: (weight*gold_18_price) + (wage_%*weight*gold_18_price) + misc_fixed + (profit_%*prev) + (VAT_%*final).
-- Dollar: dollar_price * dollar_rate from prices.json.
-- ----------------------------
CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned DEFAULT NULL,
  `product_type_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `description` text,
  `pricing_method` enum('weight','dollar') NOT NULL DEFAULT 'weight',
  `weight_gram` decimal(10,3) DEFAULT NULL COMMENT 'For weight-based',
  `wage_percent` decimal(5,2) DEFAULT NULL COMMENT 'Wage % for weight-based',
  `profit_percent` decimal(5,2) DEFAULT NULL,
  `misc_fixed` bigint(20) DEFAULT NULL COMMENT 'Fixed amount in smallest currency unit',
  `vat_percent` decimal(5,2) DEFAULT NULL,
  `price_amount` bigint(20) DEFAULT NULL COMMENT 'For dollar-based: price in foreign unit; convert with dollar_rate',
  `price_currency` varchar(10) DEFAULT 'USD',
  `stock` int(11) NOT NULL DEFAULT 0,
  `gallery` text COMMENT 'Store as JSON string. Array of image paths.',
  `custom_data` text COMMENT 'Store as JSON string. Dynamic type fields.',
  `meta` text COMMENT 'Store as JSON string. e.g. gallery_images, gallery_videos.',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort` int(11) 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`),
  KEY `idx_products_category` (`category_id`),
  KEY `idx_products_type` (`product_type_id`),
  KEY `idx_products_slug` (`slug`),
  KEY `idx_products_name` (`name`(100)),
  KEY `idx_products_active_sort` (`active`,`sort`),
  KEY `idx_products_stock` (`stock`),
  CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_products_type` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- shipping_methods (fixed cost or free pickup)
-- ----------------------------
CREATE TABLE `shipping_methods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `type` enum('fixed','free') NOT NULL DEFAULT 'fixed',
  `cost_fixed` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Amount in smallest currency unit',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort` int(11) 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`),
  KEY `idx_shipping_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- gateways
-- ----------------------------
CREATE TABLE `gateways` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `config` text COMMENT 'Store as JSON string. api_key, callback_url, etc.',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort` int(11) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- coupons (percent/fixed, all or specific, expiry, min_amount, max_uses)
-- ----------------------------
CREATE TABLE `coupons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `type` enum('percent','fixed') NOT NULL DEFAULT 'percent',
  `value` bigint(20) NOT NULL COMMENT 'Percent (e.g. 10) or fixed amount in smallest unit',
  `min_order_amount` bigint(20) DEFAULT NULL,
  `usage_limit` int(11) DEFAULT NULL COMMENT 'NULL = unlimited',
  `used_count` int(11) NOT NULL DEFAULT 0,
  `valid_from` datetime DEFAULT NULL,
  `valid_to` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `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_coupons_code` (`code`),
  KEY `idx_coupons_active_dates` (`active`,`valid_from`,`valid_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- bank_accounts (display and receipt matching; managed by admin)
-- ----------------------------
CREATE TABLE `bank_accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `iban` varchar(50) DEFAULT NULL,
  `account_number` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort` int(11) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- orders (user_id SET NULL on user delete to preserve history)
-- ----------------------------
CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `status` enum('pending','paid','processing','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',
  `subtotal` bigint(20) NOT NULL DEFAULT 0,
  `shipping_cost` bigint(20) NOT NULL DEFAULT 0,
  `discount_amount` bigint(20) NOT NULL DEFAULT 0,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `shipping_method_id` int(10) unsigned DEFAULT NULL,
  `coupon_id` int(10) unsigned DEFAULT NULL,
  `payment_method` enum('credit','gateway','mixed') NOT NULL DEFAULT 'credit',
  `gateway_id` int(10) unsigned DEFAULT NULL,
  `gateway_ref` varchar(100) DEFAULT NULL,
  `credit_amount` bigint(20) NOT NULL DEFAULT 0,
  `gateway_amount` bigint(20) NOT NULL DEFAULT 0,
  `shipping_address` text,
  `note` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_orders_user` (`user_id`),
  KEY `idx_orders_status` (`status`),
  KEY `idx_orders_created` (`created_at`),
  KEY `idx_orders_shipping_method` (`shipping_method_id`),
  KEY `idx_orders_coupon` (`coupon_id`),
  KEY `idx_orders_gateway` (`gateway_id`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_shipping` FOREIGN KEY (`shipping_method_id`) REFERENCES `shipping_methods` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_gateway` FOREIGN KEY (`gateway_id`) REFERENCES `gateways` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` bigint(20) NOT NULL,
  `weight_gram` decimal(10,3) DEFAULT NULL,
  `total_price` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_order_items_order` (`order_id`),
  KEY `idx_order_items_product` (`product_id`),
  CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_order_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- credits (money + gold weight; no negative; enforce in PHP)
-- ----------------------------
CREATE TABLE `credits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `money_balance` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Smallest currency unit',
  `gold_weight_balance` decimal(10,3) NOT NULL DEFAULT 0.000 COMMENT 'grams',
  `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_credits_user` (`user_id`),
  CONSTRAINT `fk_credits_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- credit_logs (reason, admin_id for audit)
-- ----------------------------
CREATE TABLE `credit_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `type` varchar(50) NOT NULL COMMENT 'order, receipt, adjust, melt_buy, melt_sell, etc.',
  `money_delta` bigint(20) NOT NULL DEFAULT 0,
  `gold_weight_delta` decimal(10,3) NOT NULL DEFAULT 0.000,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` int(10) unsigned DEFAULT NULL,
  `note` text,
  `admin_id` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_credit_logs_user` (`user_id`),
  KEY `idx_credit_logs_created` (`created_at`),
  KEY `idx_credit_logs_reference` (`reference_type`,`reference_id`),
  KEY `idx_credit_logs_admin` (`admin_id`),
  CONSTRAINT `fk_credit_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_credit_logs_admin` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- transactions (gateway payments; type for reports)
-- ----------------------------
CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned DEFAULT NULL,
  `gateway_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'payment' COMMENT 'payment, refund, etc.',
  `amount` bigint(20) NOT NULL,
  `status` enum('pending','success','failed','refunded') NOT NULL DEFAULT 'pending',
  `gateway_ref` varchar(100) DEFAULT NULL,
  `raw_response` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transactions_order` (`order_id`),
  KEY `idx_transactions_gateway` (`gateway_id`),
  KEY `idx_transactions_user` (`user_id`),
  KEY `idx_transactions_status` (`status`),
  KEY `idx_transactions_type_date` (`user_id`,`type`,`created_at`),
  CONSTRAINT `fk_transactions_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_transactions_gateway` FOREIGN KEY (`gateway_id`) REFERENCES `gateways` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- receipts (tracking_no, date, amount, bank_account_id; admin approve/reject)
-- ----------------------------
CREATE TABLE `receipts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `amount` bigint(20) NOT NULL,
  `bank_account_id` int(10) unsigned DEFAULT NULL,
  `tracking_no` varchar(100) DEFAULT NULL,
  `receipt_date` date DEFAULT NULL COMMENT 'Date of deposit',
  `deposit_ref` varchar(100) DEFAULT NULL,
  `proof_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(10) unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `reject_reason` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_receipts_user` (`user_id`),
  KEY `idx_receipts_status` (`status`),
  KEY `idx_receipts_created` (`created_at`),
  CONSTRAINT `fk_receipts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_receipts_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_receipts_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- delivery_requests (physical gold by weight, address, shipping cost)
-- ----------------------------
CREATE TABLE `delivery_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `order_id` int(10) unsigned DEFAULT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` int(10) unsigned DEFAULT NULL,
  `gold_weight_gram` decimal(10,3) DEFAULT NULL,
  `shipping_method_id` int(10) unsigned DEFAULT NULL,
  `shipping_cost` bigint(20) NOT NULL DEFAULT 0,
  `address` text,
  `status` enum('pending','confirmed','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_delivery_user` (`user_id`),
  KEY `idx_delivery_order` (`order_id`),
  KEY `idx_delivery_status` (`status`),
  CONSTRAINT `fk_delivery_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_delivery_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_delivery_shipping` FOREIGN KEY (`shipping_method_id`) REFERENCES `shipping_methods` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- site_contents (slider, footer; image/video/text, sortable)
-- ----------------------------
CREATE TABLE `site_contents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(50) NOT NULL COMMENT 'e.g. slider, footer, about',
  `content` longtext,
  `lang` varchar(10) NOT NULL DEFAULT 'fa',
  `sort_order` int(11) 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 `idx_site_contents_key_lang` (`key`,`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- sms_templates (SMS.ir: template_id, VerificationCode; event key)
-- ----------------------------
CREATE TABLE `sms_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(50) NOT NULL COMMENT 'Event key: e.g. login, verification, order_placed',
  `template_id` int(11) DEFAULT NULL COMMENT 'SMS.ir template ID',
  `body` text COMMENT 'Fallback body with placeholders: {code}, {name}',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `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_sms_templates_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- configs (key-value; superadmin editable)
-- ----------------------------
CREATE TABLE `configs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(100) NOT NULL,
  `value` text,
  `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_configs_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- logs (audit: user_id, action, details, timestamp)
-- ----------------------------
CREATE TABLE `logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int(10) unsigned DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `extra` text COMMENT 'JSON or text detail',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_logs_user` (`user_id`),
  KEY `idx_logs_action` (`action`),
  KEY `idx_logs_entity` (`entity_type`,`entity_id`),
  KEY `idx_logs_created` (`created_at`),
  CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================================
-- INITIAL INSERTS
-- =============================================================================

-- ----------------------------
-- configs (defaults; customize via superadmin)
-- ----------------------------
INSERT INTO `configs` (`key`, `value`) VALUES
('logo_url', ''),
('store_name', 'Gold Shop'),
('colors', '{"bg":"#ffffff","accent":"#ffd700","text":"#000000"}'),
('currency_name', 'ریال'),
('vat_percent', '0.00'),
('max_image_upload', '10'),
('max_video_upload', '5'),
('sms_apikey', 'KJxcM0Cs3atr7aAzjgJk9ZYeaIRx2Ppavebg5E5NojZGC7iu'),
('sms_url', 'https://api.sms.ir/v1/send/verify'),
('prices_api_url', 'https://central-api.example.com/prices'),
('recaptcha_sitekey', 'your-sitekey-here'),
('onesignal_appid', ''),
('price_cron_json_path', 'data/prices.json'),
('feature_melted_gold', '1'),
('feature_custom_product_fields', '1');

-- ----------------------------
-- superuser (mobile 09120000000; default password: superpass)
-- Replace password_hash with: php -r "echo password_hash('superpass', PASSWORD_BCRYPT);"
-- Then: UPDATE users SET password_hash = 'PASTE_HASH' WHERE role = 'super';
-- ----------------------------
INSERT INTO `users` (`mobile`, `password_hash`, `role`, `active`, `full_name`) VALUES
('09120000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super', 1, 'Super Admin');


-- ----------------------------
-- sample product_type (انگشتر with dynamic fields)
-- ----------------------------
INSERT INTO `product_types` (`id`, `name`, `slug`, `field_definitions`, `active`) VALUES
(1, 'انگشتر', 'ring', '{"weight":"number","image":"file","description":"text"}', 1);

-- ----------------------------
-- sample products
-- ----------------------------
INSERT INTO `products` (
  `id`, `category_id`, `product_type_id`, `name`, `slug`, `description`,
  `pricing_method`, `weight_gram`, `wage_percent`, `profit_percent`, `misc_fixed`, `vat_percent`,
  `stock`, `meta`, `active`, `sort`
) VALUES
(1, 2, 1, 'انگشتر طلا', 'gold-ring-1', 'انگشتر طلای ۱۸ عیار',
  'weight', 5.000, 25.00, 7.00, 1000000, NULL,
  10, '{"gallery_images":["img1.jpg"],"gallery_videos":[]}', 1, 0),
(2, 2, 1, 'انگشتر طلا مدل کلاسیک', 'gold-ring-classic', 'انگشتر کلاسیک زنانه',
  'weight', 4.500, 25.00, 7.00, 500000, NULL,
  5, '{"gallery_images":["img2.jpg"],"gallery_videos":[]}', 1, 1);

-- ----------------------------
-- sample bank_account
-- ----------------------------
INSERT INTO `bank_accounts` (`id`, `name`, `bank_name`, `account_number`, `active`, `sort`) VALUES
(1, 'حساب اصلی', 'بانک ملی', '1234567890', 1, 0);

-- ----------------------------
-- sample shipping_method (حضوری, free)
-- ----------------------------
INSERT INTO `shipping_methods` (`id`, `name`, `type`, `cost_fixed`, `active`, `sort`) VALUES
(1, 'حضوری', 'free', 0, 1, 0);

-- ----------------------------
-- sample sms_template (event login, template_id for SMS.ir)
-- ----------------------------
INSERT INTO `sms_templates` (`key`, `template_id`, `body`, `active`) VALUES
('login', 10001, 'کد ورود: {VerificationCode}', 1);

-- =============================================================================
-- STAGE 4: Auth columns and indexes (run after initial schema if already deployed)
-- =============================================================================

ALTER TABLE `users`
  ADD COLUMN `otp_hash` varchar(255) DEFAULT NULL COMMENT 'Hashed OTP for login (password_hash)',
  ADD COLUMN `otp_expires` datetime DEFAULT NULL,
  ADD COLUMN `failed_attempts` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'OTP verify failures',
  ADD COLUMN `last_attempt` datetime DEFAULT NULL,
  ADD COLUMN `login_attempts` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Successful logins count',
  ADD COLUMN `ip_last` varchar(45) DEFAULT NULL;

-- Index for OTP lookup and expiry checks
ALTER TABLE `users` ADD INDEX `idx_users_mobile_otp_expires` (`mobile`, `otp_expires`);

-- New config keys for auth (insert if not exists; adjust super_password_hash after first deploy)
INSERT IGNORE INTO `configs` (`key`, `value`) VALUES
('recaptcha_secret', ''),
('recaptcha_threshold', '0.5'),
('lockout_attempts', '5'),
('lockout_minutes', '30'),
('otp_expiry_seconds', '300'),
('super_username', 'superadmin'),
('super_password_hash', '');
-- Set super_password_hash via superadmin or: UPDATE configs SET value = '<bcrypt_hash>' WHERE `key` = 'super_password_hash';

-- =============================================================================
-- STAGE 5: Admin panel – operator role (run once if already deployed)
-- =============================================================================

ALTER TABLE `users` MODIFY COLUMN `role` enum('user','admin','operator','super') NOT NULL DEFAULT 'user';

-- =============================================================================
-- STAGE 6: Configs management – ensure all keys exist (run once if already deployed)
-- =============================================================================

INSERT IGNORE INTO `configs` (`key`, `value`) VALUES
('logo_url', '/assets/uploads/logo.png'),
('colors', '{"bg":"#ffffff","accent":"#ffd700","primary":"#c9a347","text":"#212529"}'),
('currency_name', 'ریال'),
('vat_percent', '0.00'),
('max_image_upload', '10'),
('max_video_upload', '5'),
('sms_apikey', 'KJxcM0Cs3atr7aAzjgJk9ZYeaIRx2Ppavebg5E5NojZGC7iu'),
('sms_url', 'https://api.sms.ir/v1/send/verify'),
('prices_api_url', 'https://api.example.com/gold-prices'),
('recaptcha_sitekey', ''),
('recaptcha_secret', ''),
('lockout_minutes', '30'),
('lockout_attempts', '5'),
('features_toggle', '{"melted_gold":true,"physical_delivery":true,"coupons":true}');
