-- Real Estate Management System - Database Schema
-- Compatible with MySQL 5.7+ and MariaDB 10.2+
-- For SQLite the schema is applied via app/Core/Database.php on first run.

-- --------------------------------------------------------
-- Users
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    password   VARCHAR(255) NOT NULL,
    phone      VARCHAR(30),
    avatar     VARCHAR(255),
    role       VARCHAR(30) NOT NULL DEFAULT 'admin', -- admin | manager | accountant
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------
-- Properties
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS properties (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER NOT NULL,
    name        VARCHAR(150) NOT NULL,
    address     TEXT NOT NULL,
    city        VARCHAR(100),
    state       VARCHAR(100),
    zip_code    VARCHAR(20),
    type        VARCHAR(30) NOT NULL DEFAULT 'house', -- house|apartment|condo|commercial|land
    description TEXT,
    lat         DECIMAL(10,8),
    lng         DECIMAL(11,8),
    image       VARCHAR(255),
    status      VARCHAR(30) NOT NULL DEFAULT 'active', -- active|inactive|under_maintenance
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- Units
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS units (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER NOT NULL,
    unit_number VARCHAR(30) NOT NULL,
    floor       INTEGER,
    bedrooms    INTEGER DEFAULT 1,
    bathrooms   INTEGER DEFAULT 1,
    area_sqm    DECIMAL(8,2),
    rent_amount DECIMAL(12,2) NOT NULL,
    description TEXT,
    status      VARCHAR(30) NOT NULL DEFAULT 'vacant', -- vacant|occupied|under_maintenance
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- Tenants
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS tenants (
    id                        INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id                   INTEGER,
    first_name                VARCHAR(100) NOT NULL,
    last_name                 VARCHAR(100) NOT NULL,
    email                     VARCHAR(150) NOT NULL UNIQUE,
    phone                     VARCHAR(30) NOT NULL,
    address                   TEXT,
    date_of_birth             DATE,
    id_type                   VARCHAR(30),  -- passport|drivers_license|national_id|sss|philhealth|other
    id_number                 VARCHAR(100),
    id_document               VARCHAR(255),
    photo                     VARCHAR(255),
    emergency_contact_name    VARCHAR(100),
    emergency_contact_phone   VARCHAR(30),
    emergency_contact_relation VARCHAR(50),
    notes                     TEXT,
    created_at                DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at                DATETIME DEFAULT CURRENT_TIMESTAMP
    ,FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- --------------------------------------------------------
-- Leases
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS leases (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    unit_id          INTEGER NOT NULL,
    tenant_id        INTEGER NOT NULL,
    start_date       DATE NOT NULL,
    end_date         DATE NOT NULL,
    monthly_rent     DECIMAL(12,2) NOT NULL,
    security_deposit DECIMAL(12,2) DEFAULT 0,
    due_day          INTEGER DEFAULT 1,       -- day of month rent is due
    reminder_days    INTEGER DEFAULT 3,       -- days before due to send reminder
    status           VARCHAR(20) DEFAULT 'active', -- active|expired|terminated
    notes            TEXT,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- Payments
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS payments (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    lease_id             INTEGER NOT NULL,
    due_date             DATE NOT NULL,
    paid_date            DATE,
    amount_due           DECIMAL(12,2) NOT NULL,
    amount_paid          DECIMAL(12,2) DEFAULT 0,
    method               VARCHAR(30),  -- cash|bank_transfer|gcash|maya|check|other
    reference_number     VARCHAR(100),
    status               VARCHAR(20) DEFAULT 'unpaid', -- unpaid|partial|paid|overdue
    receipt_path         VARCHAR(255),
    notes                TEXT,
    reminder_sent        INTEGER DEFAULT 0,
    overdue_notice_sent  INTEGER DEFAULT 0,
    created_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lease_id) REFERENCES leases(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- Expenses
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS expenses (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER NOT NULL,
    user_id     INTEGER NOT NULL,
    category    VARCHAR(50) DEFAULT 'other', -- repairs|utilities|insurance|taxes|maintenance|management_fee|advertising|legal|other
    description VARCHAR(255) NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    date        DATE NOT NULL,
    receipt_path VARCHAR(255),
    notes       TEXT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- --------------------------------------------------------
-- Maintenance Requests
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS maintenance_requests (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    unit_id          INTEGER NOT NULL,
    reported_by      INTEGER NOT NULL,
    title            VARCHAR(200) NOT NULL,
    description      TEXT NOT NULL,
    priority         VARCHAR(20) DEFAULT 'medium', -- low|medium|high|urgent
    status           VARCHAR(20) DEFAULT 'open',   -- open|in_progress|resolved|closed
    estimated_cost   DECIMAL(12,2),
    actual_cost      DECIMAL(12,2),
    assigned_to      VARCHAR(100),
    resolution_notes TEXT,
    resolved_at      DATETIME,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
    FOREIGN KEY (reported_by) REFERENCES users(id)
);

-- --------------------------------------------------------
-- Documents
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS documents (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    documentable_type   VARCHAR(50) NOT NULL,  -- property|tenant|lease|unit
    documentable_id     INTEGER NOT NULL,
    uploaded_by         INTEGER NOT NULL,
    name                VARCHAR(200) NOT NULL,
    path                VARCHAR(255) NOT NULL,
    file_type           VARCHAR(50),
    file_size           INTEGER,
    category            VARCHAR(50) DEFAULT 'other', -- lease_contract|government_id|property_photo|receipt|other
    notes               TEXT,
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- --------------------------------------------------------
-- Email Templates
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_templates (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) NOT NULL UNIQUE,
    subject     VARCHAR(255) NOT NULL,
    body        TEXT NOT NULL,
    description TEXT,
    is_active   INTEGER DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- --------------------------------------------------------
-- Notifications (in-app)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id    INTEGER NOT NULL,
    type       VARCHAR(50) NOT NULL,  -- overdue|reminder|lease_expiry|maintenance|payment
    title      VARCHAR(200) NOT NULL,
    message    TEXT NOT NULL,
    url        VARCHAR(255),
    is_read    INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- Email Logs
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_logs (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER,
    template_slug   VARCHAR(100) NOT NULL,
    recipient_email VARCHAR(150) NOT NULL,
    recipient_name  VARCHAR(150),
    subject         VARCHAR(255) NOT NULL,
    status          VARCHAR(20) NOT NULL, -- sent|failed
    error_message   TEXT,
    related_type    VARCHAR(50),
    related_id      INTEGER,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- --------------------------------------------------------
-- Income (non-rent income)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS income (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER NOT NULL,
    user_id     INTEGER NOT NULL,
    category    VARCHAR(50) DEFAULT 'other', -- parking|late_fee|utility_reimbursement|other
    description VARCHAR(255) NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    date        DATE NOT NULL,
    notes       TEXT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);