-- MokoSuiteStorage Schema
-- Copyright (C) 2026 Moko Consulting
-- SPDX-License-Identifier: GPL-3.0-or-later

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_unit_types` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `width_ft` DECIMAL(6,1) NOT NULL,
    `depth_ft` DECIMAL(6,1) NOT NULL,
    `height_ft` DECIMAL(6,1) NULL,
    `square_feet` DECIMAL(8,1) NOT NULL,
    `climate_controlled` TINYINT(1) NOT NULL DEFAULT 0,
    `drive_up` TINYINT(1) NOT NULL DEFAULT 0,
    `indoor` TINYINT(1) NOT NULL DEFAULT 1,
    `floor_level` INT NOT NULL DEFAULT 1,
    `rate_monthly` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `rate_annual` DECIMAL(10,2) NULL,
    `deposit_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `ordering` INT NOT NULL DEFAULT 0,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_active` (`active`),
    KEY `idx_climate` (`climate_controlled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_units` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `unit_type_id` INT UNSIGNED NOT NULL,
    `unit_number` VARCHAR(20) NOT NULL,
    `building` VARCHAR(50) NULL,
    `floor` INT NOT NULL DEFAULT 1,
    `status` ENUM('available','occupied','reserved','maintenance','out_of_service') NOT NULL DEFAULT 'available',
    `rate_override` DECIMAL(10,2) NULL COMMENT 'Overrides unit_type rate if set',
    `notes` TEXT NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_unit_number` (`unit_number`),
    KEY `idx_unit_type` (`unit_type_id`),
    KEY `idx_status` (`status`),
    KEY `idx_building` (`building`),
    CONSTRAINT `fk_unit_type` FOREIGN KEY (`unit_type_id`) REFERENCES `#__mokosuitestorage_unit_types`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_tenants` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `contact_id` INT UNSIGNED NULL COMMENT 'FK to CRM contacts',
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `company` VARCHAR(255) NULL,
    `email` VARCHAR(255) NULL,
    `phone` VARCHAR(50) NULL,
    `address` TEXT NULL,
    `id_type` VARCHAR(50) NULL,
    `id_number` VARCHAR(100) NULL,
    `emergency_contact_name` VARCHAR(255) NULL,
    `emergency_contact_phone` VARCHAR(50) NULL,
    `status` ENUM('active','inactive','delinquent','evicted') NOT NULL DEFAULT 'active',
    `notes` TEXT NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_contact` (`contact_id`),
    KEY `idx_status` (`status`),
    KEY `idx_name` (`last_name`, `first_name`),
    KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_leases` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `lease_number` VARCHAR(50) NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `monthly_rate` DECIMAL(10,2) NOT NULL,
    `deposit_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `deposit_status` ENUM('held','partially_refunded','refunded','forfeited') NOT NULL DEFAULT 'held',
    `billing_day` INT UNSIGNED NOT NULL DEFAULT 1,
    `auto_renew` TINYINT(1) NOT NULL DEFAULT 1,
    `status` ENUM('active','expired','terminated','pending') NOT NULL DEFAULT 'pending',
    `termination_reason` TEXT NULL,
    `notes` TEXT NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_tenant` (`tenant_id`),
    KEY `idx_unit` (`unit_id`),
    KEY `idx_status` (`status`),
    KEY `idx_dates` (`start_date`, `end_date`),
    UNIQUE KEY `idx_lease_number` (`lease_number`),
    CONSTRAINT `fk_lease_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `#__mokosuitestorage_tenants`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lease_unit` FOREIGN KEY (`unit_id`) REFERENCES `#__mokosuitestorage_units`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_access_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` INT UNSIGNED NULL,
    `unit_id` INT UNSIGNED NULL,
    `gate_code_id` INT UNSIGNED NULL,
    `access_type` ENUM('gate_entry','gate_exit','unit_unlock','unit_lock') NOT NULL DEFAULT 'gate_entry',
    `access_point` VARCHAR(100) NULL,
    `access_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `method` ENUM('gate_code','key_card','manual','override') NOT NULL DEFAULT 'gate_code',
    `granted` TINYINT(1) NOT NULL DEFAULT 1,
    `notes` TEXT NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_tenant` (`tenant_id`),
    KEY `idx_unit` (`unit_id`),
    KEY `idx_access_time` (`access_time`),
    KEY `idx_access_type` (`access_type`),
    KEY `idx_granted` (`granted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_payments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `lease_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `payment_type` ENUM('rent','deposit','late_fee','other') NOT NULL DEFAULT 'rent',
    `payment_method` VARCHAR(50) NULL,
    `payment_date` DATE NOT NULL,
    `due_date` DATE NOT NULL,
    `status` ENUM('pending','completed','failed','refunded','void') NOT NULL DEFAULT 'pending',
    `transaction_id` VARCHAR(100) NULL,
    `notes` TEXT NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lease` (`lease_id`),
    KEY `idx_tenant` (`tenant_id`),
    KEY `idx_status` (`status`),
    KEY `idx_payment_date` (`payment_date`),
    KEY `idx_due_date` (`due_date`),
    CONSTRAINT `fk_payment_lease` FOREIGN KEY (`lease_id`) REFERENCES `#__mokosuitestorage_leases`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_payment_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `#__mokosuitestorage_tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_move_ins` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `lease_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `move_type` ENUM('move_in','move_out') NOT NULL DEFAULT 'move_in',
    `scheduled_date` DATE NOT NULL,
    `actual_date` DATE NULL,
    `condition_notes` TEXT NULL,
    `photos` JSON NULL,
    `status` ENUM('scheduled','completed','cancelled') NOT NULL DEFAULT 'scheduled',
    `inspection_passed` TINYINT(1) NULL,
    `user_id` INT UNSIGNED NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lease` (`lease_id`),
    KEY `idx_tenant` (`tenant_id`),
    KEY `idx_unit` (`unit_id`),
    KEY `idx_status` (`status`),
    KEY `idx_move_type` (`move_type`),
    KEY `idx_scheduled_date` (`scheduled_date`),
    CONSTRAINT `fk_movein_lease` FOREIGN KEY (`lease_id`) REFERENCES `#__mokosuitestorage_leases`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_movein_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `#__mokosuitestorage_tenants`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_movein_unit` FOREIGN KEY (`unit_id`) REFERENCES `#__mokosuitestorage_units`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitestorage_gate_codes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` INT UNSIGNED NOT NULL,
    `code` VARCHAR(20) NOT NULL,
    `code_type` ENUM('permanent','temporary','master') NOT NULL DEFAULT 'permanent',
    `gate` VARCHAR(100) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `valid_from` DATETIME NULL,
    `valid_until` DATETIME NULL,
    `last_used` DATETIME NULL,
    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_code` (`code`),
    KEY `idx_tenant` (`tenant_id`),
    KEY `idx_active` (`active`),
    KEY `idx_code_type` (`code_type`),
    CONSTRAINT `fk_gatecode_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `#__mokosuitestorage_tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;