-- Invoice schemes for CMR (run after schema.sql)
USE cmr_ecommerce;

CREATE TABLE IF NOT EXISTS invoice_schemes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    prefix VARCHAR(50) NOT NULL DEFAULT '',
    start_from INT NOT NULL DEFAULT 1,
    number_of_digits INT NOT NULL DEFAULT 4,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Add scheme reference to sales if column does not exist
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales' AND COLUMN_NAME = 'invoice_scheme_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE sales ADD COLUMN invoice_scheme_id INT NULL AFTER invoice_number', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO invoice_schemes (name, prefix, start_from, number_of_digits, is_default) VALUES ('Default', '', 1, 4, 1);