189 lines
8.7 KiB
SQL
189 lines
8.7 KiB
SQL
-- 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;
|