CREATE TABLE IF NOT EXISTS bills (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    bill_number VARCHAR(40) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NULL,
    table_session_id BIGINT UNSIGNED NULL,
    room_session_id BIGINT UNSIGNED NULL,
    owner_customer_session_id BIGINT UNSIGNED NULL,
    split_mode ENUM('UNSPLIT','CUSTOMER','EQUAL','ITEM','SELECTED') NOT NULL DEFAULT 'UNSPLIT',
    currency CHAR(3) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    paid_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    balance_due DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('OPEN','PARTIALLY_PAID','PAID','VOID') NOT NULL DEFAULT 'OPEN',
    opened_at DATETIME NOT NULL,
    closed_at DATETIME NULL,
    voided_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_bill_public (public_id),
    UNIQUE KEY uq_bill_number (hotel_id,bill_number),
    INDEX idx_bill_hotel_status (hotel_id,status,created_at),
    INDEX idx_bill_table (table_session_id,status),
    INDEX idx_bill_room (room_session_id,status),
    CONSTRAINT fk_bill_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT,
    CONSTRAINT fk_bill_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_owner_customer FOREIGN KEY (owner_customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    -- MariaDB compatibility: bill context is enforced by BillingService.
    -- Some MariaDB releases reject CHECK constraints that reference FK columns
    -- using ON DELETE SET NULL (ERROR 1901), so chk_bill_context is intentionally omitted.
    CONSTRAINT chk_bill_money CHECK (subtotal>=0 AND discount>=0 AND tax>=0 AND total>=0 AND paid_total>=0 AND balance_due>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bill_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    bill_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    description_snapshot VARCHAR(500) NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL,
    unit_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    unit_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    unit_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('OPEN','VOID') NOT NULL DEFAULT 'OPEN',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_bill_item_public (public_id),
    UNIQUE KEY uq_bill_order_item (bill_id,order_item_id),
    INDEX idx_bill_items_bill (bill_id,status,id),
    INDEX idx_bill_items_customer (customer_session_id,bill_id),
    CONSTRAINT fk_bill_items_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE,
    CONSTRAINT fk_bill_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT,
    CONSTRAINT fk_bill_items_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_bill_items_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL,
    CONSTRAINT chk_bill_item_money CHECK (subtotal>=0 AND tax>=0 AND total>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bill_allocations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    bill_id BIGINT UNSIGNED NOT NULL,
    bill_item_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    allocation_group CHAR(26) NOT NULL,
    allocation_type ENUM('ENTIRE_BILL','CUSTOMER_ITEMS','SELECTED_ITEMS','EQUAL_SHARE','ITEM_SHARE') NOT NULL,
    share_index SMALLINT UNSIGNED NULL,
    share_count SMALLINT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('OPEN','PARTIALLY_PAID','PAID','CANCELLED') NOT NULL DEFAULT 'OPEN',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_bill_allocation_public (public_id),
    INDEX idx_bill_allocations_bill (bill_id,status,allocation_type),
    INDEX idx_bill_allocations_customer (customer_session_id,bill_id,status),
    INDEX idx_bill_allocations_item (bill_item_id,status),
    CONSTRAINT fk_bill_alloc_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE,
    CONSTRAINT fk_bill_alloc_item FOREIGN KEY (bill_item_id) REFERENCES bill_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_bill_alloc_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_alloc_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT chk_bill_alloc_amount CHECK (amount>0 AND paid_amount>=0 AND paid_amount<=amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    payment_number VARCHAR(50) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    bill_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    method ENUM('CASH','CARD','ONLINE','ROOM_CHARGE') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency CHAR(3) NOT NULL,
    status ENUM('PENDING','AUTHORIZED','PAID','FAILED','CANCELLED','REFUNDED','PARTIALLY_REFUNDED') NOT NULL DEFAULT 'PENDING',
    provider VARCHAR(100) NULL,
    provider_reference VARCHAR(190) NULL,
    idempotency_key_hash CHAR(64) NOT NULL,
    request_hash CHAR(64) NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    authorized_at DATETIME NULL,
    paid_at DATETIME NULL,
    failed_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_payment_public (public_id),
    UNIQUE KEY uq_payment_number (hotel_id,payment_number),
    UNIQUE KEY uq_payment_idempotency (hotel_id,idempotency_key_hash),
    INDEX idx_payment_bill (bill_id,status,created_at),
    INDEX idx_payment_hotel_status (hotel_id,status,created_at),
    CONSTRAINT fk_payment_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT,
    CONSTRAINT fk_payment_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE RESTRICT,
    CONSTRAINT fk_payment_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_payment_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT chk_payment_amount CHECK (amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    payment_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('CREATE','AUTHORIZE','CAPTURE','VERIFY','VOID','REFUND','ROOM_CHARGE_POST') NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('PENDING','SUCCESS','FAILED') NOT NULL,
    provider VARCHAR(100) NULL,
    provider_reference VARCHAR(190) NULL,
    request_json JSON NULL,
    response_json JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_payment_tx_public (public_id),
    INDEX idx_payment_tx_payment (payment_id,created_at),
    CONSTRAINT fk_payment_tx_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_allocations (
    payment_id BIGINT UNSIGNED NOT NULL,
    bill_allocation_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (payment_id,bill_allocation_id),
    CONSTRAINT fk_payment_alloc_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    CONSTRAINT fk_payment_alloc_bill_alloc FOREIGN KEY (bill_allocation_id) REFERENCES bill_allocations(id) ON DELETE RESTRICT,
    CONSTRAINT chk_payment_alloc_amount CHECK (amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_charge_credentials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_session_id BIGINT UNSIGNED NOT NULL,
    pin_hash VARCHAR(255) NULL,
    status ENUM('ACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_charge_credential_session (room_session_id),
    CONSTRAINT fk_room_charge_credential_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_room_charge_credential_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_charge_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    payment_id BIGINT UNSIGNED NOT NULL,
    room_session_id BIGINT UNSIGNED NOT NULL,
    room_number_snapshot VARCHAR(50) NOT NULL,
    verification_method ENUM('ROOM_GUEST','ROOM_PIN','OTP','PMS','STAFF_APPROVAL') NOT NULL,
    verification_status ENUM('PENDING','VERIFIED','REJECTED','EXPIRED') NOT NULL DEFAULT 'PENDING',
    verification_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    verified_by BIGINT UNSIGNED NULL,
    verification_notes VARCHAR(500) NULL,
    pms_reference VARCHAR(190) NULL,
    expires_at DATETIME NULL,
    verified_at DATETIME NULL,
    rejected_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_charge_request_public (public_id),
    UNIQUE KEY uq_room_charge_request_payment (payment_id),
    INDEX idx_room_charge_pending (verification_status,created_at),
    INDEX idx_room_charge_session (room_session_id,created_at),
    CONSTRAINT fk_room_charge_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    CONSTRAINT fk_room_charge_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE RESTRICT,
    CONSTRAINT fk_room_charge_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bill_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bill_id BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(40) NULL,
    new_status VARCHAR(40) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_bill_history (bill_id,created_at),
    CONSTRAINT fk_bill_history_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE,
    CONSTRAINT fk_bill_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_bill_history_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (code,name) VALUES
('billing.view','View bills and bill allocations'),
('billing.manage','Manage bills and split allocations'),
('payments.view','View payment records'),
('payments.manage','Record and manage payments'),
('room_charge.approve','Approve or reject room-charge requests')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('billing.view','billing.manage','payments.view','payments.manage','room_charge.approve');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('CASHIER','BILLING_STAFF') AND p.code IN ('billing.view','billing.manage','payments.view','payments.manage','room_charge.approve');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('billing.view','payments.view');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='READ_ONLY_MANAGER' AND p.code IN ('billing.view','payments.view');

INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type)
SELECT id,'room_charge_verification_method','STAFF_APPROVAL','STRING' FROM hotels
ON DUPLICATE KEY UPDATE setting_value=setting_value;

INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type)
SELECT id,'online_payment_enabled','0','BOOLEAN' FROM hotels
ON DUPLICATE KEY UPDATE setting_value=setting_value;