SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS backups;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS room_stays;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS daily_counters;
DROP TABLE IF EXISTS opening_balances;
DROP TABLE IF EXISTS payment_methods;
DROP TABLE IF EXISTS subcategories;
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS api_tokens;
DROP TABLE IF EXISTS user_devices;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_code VARCHAR(30) NOT NULL,
 full_name VARCHAR(100) NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 role VARCHAR(20) NOT NULL,
 phone VARCHAR(30) NULL,
 device_limit TINYINT UNSIGNED NOT NULL DEFAULT 1,
 failed_login_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
 locked_until DATETIME NULL,
 last_login_at DATETIME NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 deleted_at DATETIME NULL,
 UNIQUE KEY uk_users_user_code(user_code), KEY idx_users_role(role), KEY idx_users_active(is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 device_uuid VARCHAR(150) NOT NULL,
 device_name VARCHAR(100) NULL,
 manufacturer VARCHAR(100) NULL,
 model VARCHAR(100) NULL,
 android_version VARCHAR(30) NULL,
 app_version VARCHAR(30) NULL,
 last_ip_address VARCHAR(50) NULL,
 last_active_at DATETIME NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uk_user_device(user_id,device_uuid), KEY idx_devices_active(is_active),
 CONSTRAINT fk_devices_user FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE api_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 device_id BIGINT UNSIGNED NOT NULL,
 token_hash VARCHAR(64) NOT NULL,
 expired_at DATETIME NOT NULL,
 last_used_at DATETIME NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uk_api_token_hash(token_hash), KEY idx_token_user(user_id), KEY idx_token_expired(expired_at),
 CONSTRAINT fk_tokens_user FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_tokens_device FOREIGN KEY(device_id) REFERENCES user_devices(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_code VARCHAR(30) NOT NULL,
 transaction_type VARCHAR(20) NOT NULL,
 category_name VARCHAR(100) NOT NULL,
 description TEXT NULL,
 sort_order INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 deleted_at DATETIME NULL,
 UNIQUE KEY uk_category_code(category_code), KEY idx_category_type(transaction_type),
 CONSTRAINT fk_category_creator FOREIGN KEY(created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 room_code VARCHAR(30) NOT NULL,
 room_number VARCHAR(20) NOT NULL,
 room_name VARCHAR(100) NULL,
 room_type VARCHAR(100) NULL,
 default_price DECIMAL(15,2) NOT NULL DEFAULT 0,
 room_status VARCHAR(20) NOT NULL DEFAULT 'available',
 notes TEXT NULL,
 sort_order INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 deleted_at DATETIME NULL,
 UNIQUE KEY uk_room_code(room_code), UNIQUE KEY uk_room_number(room_number), KEY idx_room_status(room_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subcategories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_id BIGINT UNSIGNED NOT NULL,
 subcategory_code VARCHAR(30) NOT NULL,
 subcategory_name VARCHAR(100) NOT NULL,
 linked_room_id BIGINT UNSIGNED NULL,
 use_default_price TINYINT(1) NOT NULL DEFAULT 0,
 default_price DECIMAL(15,2) NULL,
 sort_order INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 deleted_at DATETIME NULL,
 UNIQUE KEY uk_subcategory_code(subcategory_code), KEY idx_subcategory_category(category_id),
 CONSTRAINT fk_subcategory_category FOREIGN KEY(category_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_subcategory_room FOREIGN KEY(linked_room_id) REFERENCES rooms(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_methods (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 payment_code VARCHAR(30) NOT NULL,
 payment_name VARCHAR(100) NOT NULL,
 sort_order INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 deleted_at DATETIME NULL,
 UNIQUE KEY uk_payment_code(payment_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opening_balances (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 amount DECIMAL(15,2) NOT NULL,
 effective_date DATE NOT NULL,
 reason TEXT NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 KEY idx_opening_date(effective_date),
 CONSTRAINT fk_opening_user FOREIGN KEY(created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE daily_counters (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 counter_date DATE NOT NULL,
 counter_name VARCHAR(30) NOT NULL,
 last_number INT UNSIGNED NOT NULL DEFAULT 0,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uk_daily_counter(counter_date,counter_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 transaction_uuid CHAR(36) NOT NULL,
 transaction_number VARCHAR(50) NOT NULL,
 transaction_type VARCHAR(20) NOT NULL,
 category_id BIGINT UNSIGNED NOT NULL,
 subcategory_id BIGINT UNSIGNED NULL,
 room_id BIGINT UNSIGNED NULL,
 amount DECIMAL(15,2) NOT NULL,
 description TEXT NULL,
 payment_method_id BIGINT UNSIGNED NOT NULL,
 employee_id BIGINT UNSIGNED NOT NULL,
 transaction_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL DEFAULT 'active',
 original_transaction_id BIGINT UNSIGNED NULL,
 correction_reason TEXT NULL,
 source VARCHAR(20) NOT NULL DEFAULT 'android',
 device_uuid VARCHAR(150) NULL,
 client_created_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uk_transaction_uuid(transaction_uuid), UNIQUE KEY uk_transaction_number(transaction_number),
 KEY idx_transaction_date(transaction_at), KEY idx_transaction_type(transaction_type), KEY idx_transaction_status(status),
 CONSTRAINT fk_transaction_category FOREIGN KEY(category_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_transaction_subcategory FOREIGN KEY(subcategory_id) REFERENCES subcategories(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_transaction_room FOREIGN KEY(room_id) REFERENCES rooms(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_transaction_payment FOREIGN KEY(payment_method_id) REFERENCES payment_methods(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_transaction_employee FOREIGN KEY(employee_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_transaction_original FOREIGN KEY(original_transaction_id) REFERENCES transactions(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE room_stays (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 room_id BIGINT UNSIGNED NOT NULL,
 transaction_id BIGINT UNSIGNED NOT NULL,
 checkin_at DATETIME NOT NULL,
 checkin_by BIGINT UNSIGNED NOT NULL,
 checkout_at DATETIME NULL,
 checkout_by BIGINT UNSIGNED NULL,
 status VARCHAR(20) NOT NULL DEFAULT 'active',
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 KEY idx_stay_room(room_id), KEY idx_stay_status(status),
 CONSTRAINT fk_stay_room FOREIGN KEY(room_id) REFERENCES rooms(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_stay_transaction FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_stay_checkin FOREIGN KEY(checkin_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
 CONSTRAINT fk_stay_checkout FOREIGN KEY(checkout_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 setting_key VARCHAR(100) NOT NULL,
 setting_value LONGTEXT NULL,
 setting_type VARCHAR(30) NOT NULL DEFAULT 'string',
 is_public TINYINT(1) NOT NULL DEFAULT 0,
 updated_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uk_setting_key(setting_key),
 CONSTRAINT fk_settings_user FOREIGN KEY(updated_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 module VARCHAR(100) NOT NULL,
 reference_type VARCHAR(100) NULL,
 reference_id BIGINT UNSIGNED NULL,
 old_data JSON NULL,
 new_data JSON NULL,
 ip_address VARCHAR(50) NULL,
 device_uuid VARCHAR(150) NULL,
 user_agent TEXT NULL,
 created_at DATETIME NOT NULL,
 KEY idx_audit_user(user_id), KEY idx_audit_action(action), KEY idx_audit_created(created_at),
 CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE backups (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 file_name VARCHAR(255) NOT NULL,
 file_path VARCHAR(500) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
 backup_type VARCHAR(30) NOT NULL,
 checksum VARCHAR(128) NOT NULL,
 status VARCHAR(30) NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL,
 CONSTRAINT fk_backup_user FOREIGN KEY(created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;