-- Smart Tech OMP / Purchase Haat completion migration
-- Generated 2026-06-24
-- Import this once AFTER the existing smarmvty_smart.sql database.

SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+06:00';
START TRANSACTION;

CREATE TABLE IF NOT EXISTS `commission_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `rule_type` varchar(50) NOT NULL DEFAULT 'percentage',
  `vendor_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `commission_value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` varchar(30) NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_commission_vendor` (`vendor_id`),
  KEY `idx_commission_category` (`category_id`),
  KEY `idx_commission_product` (`product_id`),
  KEY `idx_commission_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `message` text NOT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'new',
  `admin_note` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_contact_status` (`status`),
  KEY `idx_contact_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `email` varchar(150) NOT NULL,
  `token_hash` char(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_password_reset_token` (`token_hash`),
  KEY `idx_password_reset_user` (`user_id`),
  KEY `idx_password_reset_email` (`email`),
  KEY `idx_password_reset_expiry` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `withdraw_requests`
  ADD COLUMN IF NOT EXISTS `processed_at` datetime DEFAULT NULL AFTER `approved_by`;
ALTER TABLE `withdraw_requests`
  ADD INDEX IF NOT EXISTS `idx_withdraw_status` (`status`),
  ADD INDEX IF NOT EXISTS `idx_withdraw_processed` (`processed_at`);

ALTER TABLE `orders`
  ADD COLUMN IF NOT EXISTS `affiliate_member_id` int(11) DEFAULT NULL AFTER `customer_id`;
ALTER TABLE `orders`
  ADD INDEX IF NOT EXISTS `idx_order_affiliate` (`affiliate_member_id`);

ALTER TABLE `affiliate_conversions`
  ADD COLUMN IF NOT EXISTS `status` varchar(30) NOT NULL DEFAULT 'pending' AFTER `commission`,
  ADD COLUMN IF NOT EXISTS `processed_at` datetime DEFAULT NULL AFTER `status`;
ALTER TABLE `affiliate_conversions`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_affiliate_conversion_order` (`order_id`),
  ADD INDEX IF NOT EXISTS `idx_affiliate_conversion_status` (`status`);

ALTER TABLE `psa_transactions`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_psa_order_member` (`order_id`,`affiliate_member_id`);

ALTER TABLE `coupons`
  ADD COLUMN IF NOT EXISTS `vendor_id` int(11) DEFAULT NULL AFTER `id`;
ALTER TABLE `coupons`
  ADD INDEX IF NOT EXISTS `idx_coupon_vendor` (`vendor_id`);

ALTER TABLE `order_items`
  ADD COLUMN IF NOT EXISTS `fulfillment_status` varchar(30) NOT NULL DEFAULT 'pending' AFTER `commission_amount`,
  ADD COLUMN IF NOT EXISTS `tracking_no` varchar(150) DEFAULT NULL AFTER `fulfillment_status`;
ALTER TABLE `order_items`
  ADD INDEX IF NOT EXISTS `idx_order_item_fulfillment` (`fulfillment_status`),
  ADD INDEX IF NOT EXISTS `idx_order_item_tracking` (`tracking_no`);

ALTER TABLE `otp_verifications`
  MODIFY COLUMN `otp_code` varchar(255) DEFAULT NULL;
ALTER TABLE `otp_verifications`
  ADD INDEX IF NOT EXISTS `idx_otp_user_purpose` (`user_id`,`purpose`),
  ADD INDEX IF NOT EXISTS `idx_otp_expiry` (`expires_at`);

ALTER TABLE `product_metrics`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_product_metrics_product` (`product_id`);

ALTER TABLE `payments`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_gateway_transaction` (`gateway`,`transaction_id`),
  ADD INDEX IF NOT EXISTS `idx_payment_order` (`order_id`),
  ADD INDEX IF NOT EXISTS `idx_payment_status` (`status`);

ALTER TABLE `affiliate_products`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_affiliate_product` (`affiliate_member_id`,`product_id`);
ALTER TABLE `affiliate_members`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_affiliate_user` (`user_id`);
ALTER TABLE `affiliate_member_plans`
  ADD INDEX IF NOT EXISTS `idx_member_plan_status` (`affiliate_member_id`,`status`,`ends_at`);
ALTER TABLE `affiliate_statements`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_affiliate_statement_period` (`affiliate_member_id`,`statement_type`,`period_start`,`period_end`);
ALTER TABLE `royalty_cycles`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_royalty_cycle_period` (`cycle_type`,`period_start`,`period_end`);
ALTER TABLE `royalty_distributions`
  ADD UNIQUE INDEX IF NOT EXISTS `uniq_royalty_distribution_member` (`royalty_cycle_id`,`affiliate_member_id`);

INSERT INTO `commission_rules` (`name`,`rule_type`,`commission_value`,`status`,`created_at`,`updated_at`)
SELECT 'Default marketplace commission','percentage',5.00,'active',NOW(),NOW()
WHERE NOT EXISTS (SELECT 1 FROM `commission_rules` WHERE `name`='Default marketplace commission');

COMMIT;
