-- ============================================================
-- SalonPro X - Database Triggers
-- ============================================================

USE `salonprox_db`;

DELIMITER $$

-- Auto-update customer total_visits and last_visit_date on invoice creation
CREATE TRIGGER `trg_after_invoice_insert`
AFTER INSERT ON `invoices`
FOR EACH ROW
BEGIN
    UPDATE `customers`
    SET `total_visits` = `total_visits` + 1,
        `total_spent` = `total_spent` + NEW.`grand_total`,
        `last_visit_date` = NEW.`created_at`
    WHERE `id` = NEW.`customer_id`;
END$$

-- Auto-deduct stock on invoice item insert for products
CREATE TRIGGER `trg_after_invoice_item_insert`
AFTER INSERT ON `invoice_items`
FOR EACH ROW
BEGIN
    IF NEW.`reference_type` = 'product' THEN
        UPDATE `products`
        SET `current_stock` = `current_stock` - NEW.`quantity`
        WHERE `id` = NEW.`reference_id`;
        
        INSERT INTO `stock_movements`
            (`product_id`, `branch_id`, `movement_type`, `quantity`, `before_quantity`, `after_quantity`, `reference_type`, `reference_id`, `created_by`)
        SELECT
            `p`.`id`, `p`.`branch_id`, 'sale', -(NEW.`quantity`), `p`.`current_stock` + NEW.`quantity`, `p`.`current_stock`, 'invoice', NEW.`invoice_id`, NULL
        FROM `invoices` `i`
        JOIN `products` `p` ON `p`.`id` = NEW.`reference_id`
        WHERE `i`.`id` = NEW.`invoice_id`;
    END IF;
END$$

-- Auto-update stock on GRN item insert
CREATE TRIGGER `trg_after_grn_item_insert`
AFTER INSERT ON `grn_items`
FOR EACH ROW
BEGIN
    UPDATE `products`
    SET `current_stock` = `current_stock` + NEW.`quantity_accepted`
    WHERE `id` = NEW.`product_id`;
    
    INSERT INTO `stock_movements`
        (`product_id`, `branch_id`, `movement_type`, `quantity`, `before_quantity`, `after_quantity`, `reference_type`, `reference_id`, `created_by`)
    SELECT
        `p`.`id`, `p`.`branch_id`, 'purchase', NEW.`quantity_accepted`, `p`.`current_stock` - NEW.`quantity_accepted`, `p`.`current_stock`, 'grn', NEW.`grn_id`, NULL
    FROM `products` `p`
    WHERE `p`.`id` = NEW.`product_id`;
    
    IF NEW.`batch_number` IS NOT NULL AND NEW.`batch_number` != '' THEN
        INSERT INTO `product_batches` (`product_id`, `batch_number`, `expiry_date`, `quantity`, `cost_price`, `received_date`)
        VALUES (NEW.`product_id`, NEW.`batch_number`, NEW.`expiry_date`, NEW.`quantity_accepted`, NEW.`unit_cost`, CURDATE());
    END IF;
END$$

-- Auto-update stock on stock transfer completion
CREATE TRIGGER `trg_stock_transfer_complete`
AFTER UPDATE ON `stock_transfers`
FOR EACH ROW
BEGIN
    IF NEW.`status` = 'completed' AND OLD.`status` != 'completed' THEN
        UPDATE `products` `p`
        INNER JOIN `stock_transfer_items` `sti` ON `sti`.`product_id` = `p`.`id`
        SET `p`.`current_stock` = `p`.`current_stock` - `sti`.`quantity`
        WHERE `sti`.`stock_transfer_id` = NEW.`id` AND `p`.`branch_id` = NEW.`from_branch_id`;
        
        UPDATE `products` `p`
        INNER JOIN `stock_transfer_items` `sti` ON `sti`.`product_id` = `p`.`id`
        SET `p`.`current_stock` = `p`.`current_stock` + `sti`.`quantity`
        WHERE `sti`.`stock_transfer_id` = NEW.`id` AND `p`.`branch_id` = NEW.`to_branch_id`;
    END IF;
END$$

-- Auto-calculate commission on invoice payment
CREATE TRIGGER `trg_after_invoice_payment_insert`
AFTER INSERT ON `invoice_payments`
FOR EACH ROW
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_employee_id INT UNSIGNED;
    DECLARE v_service_id INT UNSIGNED;
    DECLARE v_service_price DECIMAL(10,2);
    DECLARE v_commission_type VARCHAR(20);
    DECLARE v_commission_value DECIMAL(10,2);
    DECLARE v_commission_amount DECIMAL(10,2);
    
    DECLARE cur CURSOR FOR
        SELECT `asi`.`employee_id`, `asi`.`service_id`, `asi`.`price`, `s`.`commission_type`, `s`.`commission_value`
        FROM `invoice_items` `ii`
        JOIN `appointment_services` `asi` ON `asi`.`appointment_id` = `ii`.`reference_id`
        JOIN `services` `s` ON `s`.`id` = `asi`.`service_id`
        WHERE `ii`.`invoice_id` = NEW.`invoice_id`
        AND `ii`.`reference_type` = 'service';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_employee_id, v_service_id, v_service_price, v_commission_type, v_commission_value;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        IF v_employee_id IS NOT NULL THEN
            IF v_commission_type = 'percentage' THEN
                SET v_commission_amount = (v_service_price * v_commission_value) / 100;
            ELSE
                SET v_commission_amount = v_commission_value;
            END IF;
            
            INSERT INTO `employee_commissions`
                (`employee_id`, `invoice_id`, `service_id`, `commission_amount`, `calculated_date`, `paid_status`)
            VALUES
                (v_employee_id, NEW.`invoice_id`, v_service_id, v_commission_amount, CURDATE(), 'pending');
        END IF;
    END LOOP;
    
    CLOSE cur;
END$$

-- Auto-update cash book on expense
CREATE TRIGGER `trg_after_expense_insert`
AFTER INSERT ON `expenses`
FOR EACH ROW
BEGIN
    DECLARE last_balance DECIMAL(12,2) DEFAULT 0;
    
    SELECT `balance` INTO last_balance
    FROM `cash_book`
    WHERE `branch_id` = NEW.`branch_id`
    ORDER BY `id` DESC
    LIMIT 1;
    
    IF last_balance IS NULL THEN
        SET last_balance = 0;
    END IF;
    
    INSERT INTO `cash_book`
        (`branch_id`, `transaction_date`, `description`, `type`, `amount`, `balance`, `reference_type`, `reference_id`, `created_by`)
    VALUES
        (NEW.`branch_id`, NEW.`date`, CONCAT('Expense: ', NEW.`description`), 'outflow', NEW.`amount`, last_balance - NEW.`amount`, 'expense', NEW.`id`, NEW.`created_by`);
END$$

DELIMITER ;
