CREATE TABLE IF NOT EXISTS accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 slug VARCHAR(255) UNIQUE NULL,
 subdomain VARCHAR(255) UNIQUE NULL,
 domain VARCHAR(255) NULL,
 email VARCHAR(255) NULL,
 phone VARCHAR(255) NULL,
 logo_path VARCHAR(255) NULL,
 brand_color VARCHAR(20) DEFAULT '#f59e0b',
 status ENUM('active','disabled') DEFAULT 'active',
 payment_status ENUM('paid','unpaid') DEFAULT 'paid',
 subscription_amount DECIMAL(15,2) DEFAULT 0,
 subscription_started_at DATE NULL,
 subscription_expires_at DATE NULL,
 disabled_at TIMESTAMP NULL,
 disabled_reason TEXT NULL,
 notes TEXT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NULL,
 room_name VARCHAR(255) NOT NULL,
 room_code VARCHAR(255) NOT NULL UNIQUE,
 room_type VARCHAR(255) NULL,
 default_price DECIMAL(15,2) DEFAULT 0,
 max_occupancy INT DEFAULT 1,
 status ENUM('available','occupied','maintenance','inactive') DEFAULT 'available',
 description TEXT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT rooms_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bookings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NULL,
 room_id BIGINT UNSIGNED NOT NULL,
 agent_id BIGINT UNSIGNED NULL,
 booking_reference VARCHAR(255) NOT NULL UNIQUE,
 guest_name VARCHAR(255) NOT NULL,
 guest_phone VARCHAR(50) NOT NULL,
 guest_email VARCHAR(255) NULL,
 guest_address TEXT NULL,
 guest_gender ENUM('male','female','other') NULL,
 check_in DATE NOT NULL,
 check_out DATE NOT NULL,
 booking_amount DECIMAL(15,2) DEFAULT 0,
 amount_paid DECIMAL(15,2) DEFAULT 0,
 status ENUM('awaiting_payment','confirmed','checked_in','checked_out','cancelled') DEFAULT 'awaiting_payment',
 payment_confirmed_at TIMESTAMP NULL,
 access_issued_at TIMESTAMP NULL,
 notes TEXT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT bookings_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
 CONSTRAINT bookings_room_id_foreign FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE RESTRICT,
 CONSTRAINT bookings_agent_id_foreign FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS exception_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NULL,
 booking_id BIGINT UNSIGNED NULL,
 payment_id BIGINT UNSIGNED NULL,
 room_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 exception_type VARCHAR(255) DEFAULT 'general',
 action VARCHAR(255) NULL,
 description TEXT NOT NULL,
 reason TEXT NULL,
 severity ENUM('low','medium','high','critical') DEFAULT 'medium',
 status ENUM('open','reviewing','resolved','dismissed') DEFAULT 'open',
 resolved_at TIMESTAMP NULL,
 resolved_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NOT NULL,
 reference VARCHAR(255) NOT NULL UNIQUE,
 amount DECIMAL(15,2) DEFAULT 0,
 status ENUM('pending','confirmed','failed') DEFAULT 'pending',
 gateway VARCHAR(50) DEFAULT 'manual',
 gateway_reference VARCHAR(255) NULL,
 gateway_payload JSON NULL,
 subscription_started_at DATE NULL,
 subscription_expires_at DATE NULL,
 paid_at TIMESTAMP NULL,
 confirmed_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT subscription_payments_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
 CONSTRAINT subscription_payments_confirmed_by_foreign FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
