CREATE TABLE IF NOT EXISTS roles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL UNIQUE, scope ENUM('admin','staff','tenant','maintainer') NOT NULL DEFAULT 'staff', permissions TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, phone VARCHAR(40), active TINYINT NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(role_id) REFERENCES roles(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS properties (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, address VARCHAR(255) NOT NULL, city VARCHAR(100) NOT NULL, type VARCHAR(40) NOT NULL, description TEXT, image_path VARCHAR(255), published TINYINT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS units (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, property_id INT UNSIGNED NOT NULL, name VARCHAR(60) NOT NULL, bedrooms INT NOT NULL DEFAULT 1, bathrooms INT NOT NULL DEFAULT 1, area DECIMAL(10,2) NOT NULL DEFAULT 0, rent DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('available','maintenance','inactive') NOT NULL DEFAULT 'available', UNIQUE(property_id,name), FOREIGN KEY(property_id) REFERENCES properties(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tenants (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL UNIQUE, name VARCHAR(120) NOT NULL, email VARCHAR(190), phone VARCHAR(40) NOT NULL, emergency_contact VARCHAR(200), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS maintainers (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL UNIQUE, name VARCHAR(120) NOT NULL, email VARCHAR(190), phone VARCHAR(40) NOT NULL, specialty VARCHAR(100) NOT NULL, status ENUM('available','busy','inactive') NOT NULL DEFAULT 'available', FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS agreements (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id INT UNSIGNED NOT NULL, unit_id INT UNSIGNED NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, rent DECIMAL(12,2) NOT NULL, deposit DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('draft','active','ended') NOT NULL DEFAULT 'draft', terms TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(unit_id) REFERENCES units(id), active_unit_id INT UNSIGNED GENERATED ALWAYS AS (CASE WHEN status='active' THEN unit_id ELSE NULL END) STORED, UNIQUE(active_unit_id), INDEX(unit_id,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoices (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, agreement_id INT UNSIGNED NOT NULL, period DATE NOT NULL, due_date DATE NOT NULL, amount DECIMAL(12,2) NOT NULL, status ENUM('issued','void') NOT NULL DEFAULT 'issued', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, active_period DATE GENERATED ALWAYS AS (CASE WHEN status='issued' THEN period ELSE NULL END) STORED, UNIQUE(agreement_id,active_period), FOREIGN KEY(agreement_id) REFERENCES agreements(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS transactions (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, type ENUM('income','expense') NOT NULL, category VARCHAR(100) NOT NULL, amount DECIMAL(12,2) NOT NULL, transaction_date DATE NOT NULL, method VARCHAR(60) NOT NULL, reference VARCHAR(150), property_id INT UNSIGNED NULL, invoice_id INT UNSIGNED NULL, description TEXT, status ENUM('posted','void') NOT NULL DEFAULT 'posted', created_by INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(property_id) REFERENCES properties(id), FOREIGN KEY(invoice_id) REFERENCES invoices(id), FOREIGN KEY(created_by) REFERENCES users(id), INDEX(transaction_date,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS maintenance (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id INT UNSIGNED NOT NULL, tenant_id INT UNSIGNED NULL, maintainer_id INT UNSIGNED NULL, title VARCHAR(160) NOT NULL, description TEXT NOT NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('open','assigned','in_progress','resolved','closed') NOT NULL DEFAULT 'open', estimated_cost DECIMAL(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(tenant_id) REFERENCES tenants(id), FOREIGN KEY(maintainer_id) REFERENCES maintainers(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tickets (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, created_by INT UNSIGNED NOT NULL, subject VARCHAR(160) NOT NULL, message TEXT NOT NULL, priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal', status ENUM('open','in_progress','closed') NOT NULL DEFAULT 'open', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ticket_replies (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ticket_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, message TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(ticket_id) REFERENCES tickets(id), FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS contacts (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(190), phone VARCHAR(40), type VARCHAR(50) NOT NULL, company VARCHAR(150), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS inquiries (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, property_id INT UNSIGNED NULL, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL, phone VARCHAR(40), message TEXT NOT NULL, status ENUM('new','contacted','closed') NOT NULL DEFAULT 'new', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(property_id) REFERENCES properties(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings (setting_key VARCHAR(80) PRIMARY KEY, setting_value TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_log (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, action VARCHAR(40) NOT NULL, module VARCHAR(60) NOT NULL, record_id INT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS rate_limits (bucket VARCHAR(100) PRIMARY KEY, attempts INT NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;