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 users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NULL,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 phone VARCHAR(50) NULL,
 email_verified_at TIMESTAMP NULL,
 password VARCHAR(255) NOT NULL,
 role ENUM('admin','user','staff','agent') DEFAULT 'user',
 status ENUM('active','disabled') DEFAULT 'active',
 remember_token VARCHAR(100) NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT users_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET 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 payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NULL,
 booking_id BIGINT UNSIGNED NOT NULL,
 reference VARCHAR(255) NOT NULL UNIQUE,
 amount_expected DECIMAL(15,2) DEFAULT 0,
 amount_paid DECIMAL(15,2) DEFAULT 0,
 amount DECIMAL(15,2) DEFAULT 0,
 status ENUM('pending_verification','confirmed','failed','partial_payment') DEFAULT 'pending_verification',
 gateway VARCHAR(50) DEFAULT 'paystack',
 gateway_reference VARCHAR(255) NULL,
 gateway_status VARCHAR(255) NULL,
 gateway_response VARCHAR(255) NULL,
 authorization_url VARCHAR(500) NULL,
 access_code VARCHAR(255) NULL,
 gateway_payload JSON NULL,
 payment_instruction TEXT NULL,
 paid_at TIMESTAMP NULL,
 verified_at TIMESTAMP NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT payments_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
 CONSTRAINT payments_booking_id_foreign FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS access_codes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 account_id BIGINT UNSIGNED NULL,
 booking_id BIGINT UNSIGNED NOT NULL,
 room_id BIGINT UNSIGNED NOT NULL,
 issued_by BIGINT UNSIGNED NULL,
 code VARCHAR(255) NOT NULL UNIQUE,
 token VARCHAR(255) NULL,
 qr_path VARCHAR(255) NULL,
 status ENUM('active','expired','revoked') DEFAULT 'active',
 issued_at DATETIME NULL,
 expires_at DATETIME NULL,
 revoked_at DATETIME NULL,
 notes TEXT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT access_codes_account_id_foreign FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
 CONSTRAINT access_codes_booking_id_foreign FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
 CONSTRAINT access_codes_room_id_foreign FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE RESTRICT,
 CONSTRAINT access_codes_issued_by_foreign FOREIGN KEY (issued_by) 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;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
 email VARCHAR(255) PRIMARY KEY,
 token VARCHAR(255) NOT NULL,
 created_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (name,email,password,role,status,created_at,updated_at)
VALUES ('SmartGrid Admin','admin@smartgridautomations.ng','$2y$12$KIXNML54Y8US0P/xX6meZO9rGqH.OiM30tHEkKnsnX.E9IIZhVnKy','admin','active',NOW(),NOW());