-- Dynamic discount rules for POS / invoices
CREATE TABLE IF NOT EXISTS discount_rules (
    discount_rule_id INT AUTO_INCREMENT PRIMARY KEY,
    discount_rule_name VARCHAR(255) NOT NULL,
    discount_rule_is_active TINYINT(1) NOT NULL DEFAULT 1,
    discount_rule_priority INT NOT NULL DEFAULT 0,
    condition_field ENUM('cost_price', 'unit_price') NOT NULL DEFAULT 'cost_price',
    condition_operator ENUM('always', 'gt', 'gte', 'lt', 'lte', 'between') NOT NULL DEFAULT 'always',
    condition_value_min DECIMAL(12,2) DEFAULT NULL,
    condition_value_max DECIMAL(12,2) DEFAULT NULL,
    limit_type ENUM('cost_markup', 'price_discount', 'min_unit_price') NOT NULL DEFAULT 'cost_markup',
    limit_value DECIMAL(10,2) NOT NULL DEFAULT 15,
    discount_rule_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    discount_rule_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default: legacy behavior (discount only down to cost + 15%)
INSERT INTO discount_rules (
    discount_rule_name,
    discount_rule_is_active,
    discount_rule_priority,
    condition_field,
    condition_operator,
    limit_type,
    limit_value
) SELECT
    'Default cost markup 15%',
    1,
    0,
    'cost_price',
    'always',
    'cost_markup',
    15
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM discount_rules WHERE condition_operator = 'always' AND limit_type = 'cost_markup' AND limit_value = 15
);