-- Booking setup tables + default color rules.
-- Run once if you prefer SQL migration before opening the PHP setup pages.

CREATE TABLE IF NOT EXISTS booking_sources (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_booking_sources_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS booking_statuses LIKE booking_sources;
CREATE TABLE IF NOT EXISTS booking_payment_statuses LIKE booking_sources;
CREATE TABLE IF NOT EXISTS booking_payment_due_types LIKE booking_sources;

CREATE TABLE IF NOT EXISTS booking_color_codes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rule_key VARCHAR(80) NOT NULL,
    rule_name VARCHAR(150) NOT NULL,
    booking_status_code VARCHAR(60) NULL,
    payment_due_code VARCHAR(60) NULL,
    payment_status_code VARCHAR(60) NULL,
    booking_source_code VARCHAR(60) NULL,
    invoiced_condition ENUM('any','yes','no') NOT NULL DEFAULT 'any',
    balance_condition ENUM('any','has_balance','no_balance') NOT NULL DEFAULT 'any',
    bg_color VARCHAR(20) NOT NULL DEFAULT '#9CA3AF',
    text_color VARCHAR(20) NOT NULL DEFAULT '#FFFFFF',
    priority_no INT NOT NULL DEFAULT 100,
    css_class VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_booking_color_codes_key (rule_key),
    INDEX idx_booking_color_codes_priority (is_active, priority_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE orders ADD COLUMN invoiced TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN booking_source VARCHAR(60) NULL;
ALTER TABLE orders ADD COLUMN payment_due VARCHAR(60) NULL;
ALTER TABLE orders ADD COLUMN invoice_number VARCHAR(120) NULL;
ALTER TABLE orders ADD COLUMN qrCodeUrl VARCHAR(500) NULL;
ALTER TABLE orders ADD COLUMN verificationCode VARCHAR(120) NULL;
ALTER TABLE orders ADD COLUMN fiscalDeviceId VARCHAR(120) NULL;
ALTER TABLE orders ADD COLUMN globalNumber VARCHAR(120) NULL;
ALTER TABLE orders ADD COLUMN fiscalDayNumber VARCHAR(120) NULL;
ALTER TABLE orders ADD COLUMN payment_status VARCHAR(60) NULL;

INSERT INTO role_permissions(role_key, permission_key, can_view, can_create, can_edit, can_delete, can_approve)
VALUES
('admin','booking_sources',1,1,1,1,1),
('admin','booking_statuses',1,1,1,1,1),
('admin','booking_payment_statuses',1,1,1,1,1),
('admin','booking_payment_due_types',1,1,1,1,1),
('admin','booking_color_rules',1,1,1,1,1)
ON DUPLICATE KEY UPDATE can_view=1, can_create=1, can_edit=1, can_delete=1, can_approve=1;
