-- GS Store MNG - Schema Migration
-- Adds supplier/approval infrastructure to the shared database.
-- Run once after the base schema.sql.

USE `roscaw_gs-store`;

-- 1. Add role to users (client = public app buyer, supplier = MNG supplier, admin = MNG admin)
ALTER TABLE `users`
    ADD COLUMN `role` ENUM('client', 'supplier', 'admin') NOT NULL DEFAULT 'client' AFTER `profile_image`;

-- 2. Supplier profiles (business info, contact, payment details)
CREATE TABLE IF NOT EXISTS `supplier_profiles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `business_name` VARCHAR(150) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `mobile_number` VARCHAR(20) NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `payment_info` JSON NOT NULL,
    `approved` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_supplier_user` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 3. Extend products for supplier ownership + approval workflow + variants
ALTER TABLE `products`
    ADD COLUMN `supplier_id` INT DEFAULT NULL AFTER `category_id`,
    ADD COLUMN `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'approved' AFTER `is_featured`,
    ADD COLUMN `rejection_reason` VARCHAR(500) DEFAULT NULL AFTER `status`,
    ADD COLUMN `video` VARCHAR(255) DEFAULT NULL AFTER `image`,
    ADD COLUMN `sizes` JSON DEFAULT NULL AFTER `images`,
    ADD COLUMN `colors` JSON DEFAULT NULL AFTER `sizes`,
    ADD CONSTRAINT `fk_products_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    ADD INDEX `idx_products_status` (`status`),
    ADD INDEX `idx_products_supplier` (`supplier_id`);

-- 4. Create the admin account by running admin_seed.php at the site root
--    (or run this manually after generating a bcrypt hash):
--    INSERT INTO users (name, email, password, phone, role) VALUES ('Admin', 'sagabesilas@gmail.com', '<bcrypt-hash>', '0000000000', 'admin');