-- ============================================================
-- BDOZ PHP – Migration Script
-- Safe to run on an existing database.
-- Adds only the new tables and columns from the PHP conversion.
-- ============================================================

-- Partner store enhancements
ALTER TABLE student_stores 
    ADD COLUMN IF NOT EXISTS logo VARCHAR(255) NULL AFTER api_token,
    ADD COLUMN IF NOT EXISTS primary_color VARCHAR(20) NULL DEFAULT '#2563eb' AFTER logo,
    ADD COLUMN IF NOT EXISTS custom_domain VARCHAR(100) NULL AFTER primary_color,
    ADD COLUMN IF NOT EXISTS gateway_mode ENUM('bdoz','custom','both') NOT NULL DEFAULT 'bdoz' AFTER custom_domain,
    ADD COLUMN IF NOT EXISTS custom_gateway_key VARCHAR(255) NULL AFTER gateway_mode;

-- Order status: add on_hold if missing
ALTER TABLE orders 
    MODIFY COLUMN status ENUM('pending','processing','completed','failed','on_hold') NOT NULL DEFAULT 'pending';

-- Worker custom prices
CREATE TABLE IF NOT EXISTS `store_worker_prices` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` BIGINT UNSIGNED NOT NULL,
    `package_id` BIGINT UNSIGNED NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_store_package` (`store_id`, `package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Promo codes
CREATE TABLE IF NOT EXISTS `promo_codes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(30) NOT NULL UNIQUE,
    `discount_type` ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
    `discount_value` DECIMAL(10,2) NOT NULL,
    `max_uses` INT NULL,
    `used_count` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `expires_at` DATETIME NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Worker invites
CREATE TABLE IF NOT EXISTS `worker_invites` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` BIGINT UNSIGNED NOT NULL,
    `email` VARCHAR(120) NULL,
    `phone` VARCHAR(20) NULL,
    `token` VARCHAR(64) NOT NULL UNIQUE,
    `status` ENUM('pending','accepted','expired') NOT NULL DEFAULT 'pending',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activity log
CREATE TABLE IF NOT EXISTS `activity_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` BIGINT UNSIGNED NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `action` VARCHAR(100) NOT NULL,
    `details` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_store` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings
CREATE TABLE IF NOT EXISTS `settings` (
    `key` VARCHAR(50) NOT NULL PRIMARY KEY,
    `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO settings (`key`, `value`) VALUES ('maintenance_mode', '0');

-- Support tickets
CREATE TABLE IF NOT EXISTS `support_tickets` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `subject` VARCHAR(200) NOT NULL,
    `message` TEXT NOT NULL,
    `status` ENUM('open','closed') NOT NULL DEFAULT 'open',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Announcements
CREATE TABLE IF NOT EXISTS `announcements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL,
    `body` TEXT NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Done
SELECT 'Migration complete' AS status;