-- =====================================================================
-- 001_initial_schema.sql
-- Run this once on a fresh database to create all tables.
--
--   mysql -u root -p < database/migrations/001_initial_schema.sql
--
-- =====================================================================

CREATE DATABASE IF NOT EXISTS society_mgmt
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE society_mgmt;

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS super_admins (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    mobile          VARCHAR(15)  NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    is_active       TINYINT(1)   DEFAULT 1,
    last_login_at   DATETIME     NULL,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS societies (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(200) NOT NULL,
    registration_number VARCHAR(100) NULL,
    address             TEXT         NOT NULL,
    city                VARCHAR(100) NOT NULL,
    state               VARCHAR(100) NOT NULL,
    pincode             VARCHAR(10)  NOT NULL,
    contact_email       VARCHAR(150) NULL,
    contact_phone       VARCHAR(15)  NULL,
    total_blocks        INT          DEFAULT 0,
    total_flats         INT          DEFAULT 0,
    maintenance_type    ENUM('fixed','fixed_plus_variable') DEFAULT 'fixed',
    billing_cycle       ENUM('monthly','quarterly','half_yearly','yearly') DEFAULT 'monthly',
    due_day             TINYINT      DEFAULT 10,
    late_fee_type       ENUM('none','fixed','percentage') DEFAULT 'none',
    late_fee_value      DECIMAL(10,2) DEFAULT 0,
    grace_period_days   INT          DEFAULT 0,
    receipt_prefix      VARCHAR(10)  DEFAULT 'RCP',
    invoice_prefix      VARCHAR(10)  DEFAULT 'INV',
    status              ENUM('pending_approval','active','suspended','rejected') DEFAULT 'pending_approval',
    approved_by         INT UNSIGNED NULL,
    approved_at         DATETIME     NULL,
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (approved_by) REFERENCES super_admins(id) ON DELETE SET NULL,
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS society_admins (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id          INT UNSIGNED NOT NULL,
    name                VARCHAR(100) NOT NULL,
    email               VARCHAR(150) NOT NULL,
    mobile              VARCHAR(15)  NOT NULL,
    password_hash       VARCHAR(255) NULL,
    role                ENUM('chairman','secretary','treasurer','committee_member') DEFAULT 'committee_member',
    designation         VARCHAR(100) NULL,
    email_verified_at   DATETIME     NULL,
    mobile_verified_at  DATETIME     NULL,
    is_primary          TINYINT(1)   DEFAULT 0,
    is_active           TINYINT(1)   DEFAULT 1,
    last_login_at       DATETIME     NULL,
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE CASCADE,
    UNIQUE KEY uk_society_email  (society_id, email),
    UNIQUE KEY uk_society_mobile (society_id, mobile),
    INDEX idx_society (society_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS blocks (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id      INT UNSIGNED NOT NULL,
    name            VARCHAR(50)  NOT NULL,
    description     VARCHAR(255) NULL,
    total_floors    INT          DEFAULT 0,
    total_flats     INT          DEFAULT 0,
    display_order   INT          DEFAULT 0,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE CASCADE,
    UNIQUE KEY uk_society_block (society_id, name),
    INDEX idx_society (society_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS flats (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id                  INT UNSIGNED NOT NULL,
    block_id                    INT UNSIGNED NOT NULL,
    flat_number                 VARCHAR(20)  NOT NULL,
    floor                       INT          NULL,
    flat_type                   VARCHAR(20)  NULL,
    carpet_area_sqft            DECIMAL(8,2) NULL,
    built_up_area_sqft          DECIMAL(8,2) NULL,
    occupancy_status            ENUM('vacant','owner_occupied','rented','closed') DEFAULT 'vacant',
    fixed_maintenance_amount    DECIMAL(10,2) DEFAULT 0,
    created_at                  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at                  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE CASCADE,
    FOREIGN KEY (block_id)   REFERENCES blocks(id)    ON DELETE CASCADE,
    UNIQUE KEY uk_block_flat (block_id, flat_number),
    INDEX idx_society (society_id),
    INDEX idx_block   (block_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS residents (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id          INT UNSIGNED NOT NULL,
    name                VARCHAR(100) NOT NULL,
    email               VARCHAR(150) NULL,
    mobile              VARCHAR(15)  NOT NULL,
    alternate_mobile    VARCHAR(15)  NULL,
    email_verified_at   DATETIME     NULL,
    mobile_verified_at  DATETIME     NULL,
    profile_photo       VARCHAR(255) NULL,
    is_active           TINYINT(1)   DEFAULT 1,
    last_login_at       DATETIME     NULL,
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE CASCADE,
    UNIQUE KEY uk_society_mobile (society_id, mobile),
    INDEX idx_society (society_id),
    INDEX idx_mobile  (mobile)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS flat_residents (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    flat_id         INT UNSIGNED NOT NULL,
    resident_id     INT UNSIGNED NOT NULL,
    relationship    ENUM('owner','tenant','family_member') NOT NULL,
    is_primary      TINYINT(1)   DEFAULT 0,
    move_in_date    DATE         NULL,
    move_out_date   DATE         NULL,
    is_active       TINYINT(1)   DEFAULT 1,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (flat_id)     REFERENCES flats(id)     ON DELETE CASCADE,
    FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE,
    INDEX idx_flat     (flat_id),
    INDEX idx_resident (resident_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS maintenance_heads (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id      INT UNSIGNED NOT NULL,
    name            VARCHAR(100) NOT NULL,
    code            VARCHAR(50)  NOT NULL,
    charge_type     ENUM('fixed_per_flat','per_sqft','variable','one_time') DEFAULT 'fixed_per_flat',
    default_amount  DECIMAL(10,2) DEFAULT 0,
    is_active       TINYINT(1)   DEFAULT 1,
    display_order   INT          DEFAULT 0,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE CASCADE,
    UNIQUE KEY uk_society_code (society_id, code),
    INDEX idx_society (society_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bills (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id      INT UNSIGNED  NOT NULL,
    flat_id         INT UNSIGNED  NOT NULL,
    bill_number     VARCHAR(50)   NOT NULL UNIQUE,
    billing_month   TINYINT       NOT NULL,
    billing_year    SMALLINT      NOT NULL,
    bill_date       DATE          NOT NULL,
    due_date        DATE          NOT NULL,
    subtotal        DECIMAL(10,2) DEFAULT 0,
    late_fee        DECIMAL(10,2) DEFAULT 0,
    discount        DECIMAL(10,2) DEFAULT 0,
    total_amount    DECIMAL(10,2) DEFAULT 0,
    amount_paid     DECIMAL(10,2) DEFAULT 0,
    balance_due     DECIMAL(10,2) DEFAULT 0,
    status          ENUM('pending','partially_paid','paid','overdue','cancelled') DEFAULT 'pending',
    notes           TEXT          NULL,
    generated_by    INT UNSIGNED  NOT NULL,
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id)   REFERENCES societies(id)       ON DELETE CASCADE,
    FOREIGN KEY (flat_id)      REFERENCES flats(id)           ON DELETE CASCADE,
    FOREIGN KEY (generated_by) REFERENCES society_admins(id),
    UNIQUE KEY uk_flat_period   (flat_id, billing_month, billing_year),
    INDEX idx_society_status    (society_id, status),
    INDEX idx_due_date          (due_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bill_items (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bill_id                 INT UNSIGNED  NOT NULL,
    maintenance_head_id     INT UNSIGNED  NOT NULL,
    description             VARCHAR(255)  NOT NULL,
    amount                  DECIMAL(10,2) NOT NULL,
    created_at              DATETIME      DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bill_id)             REFERENCES bills(id)             ON DELETE CASCADE,
    FOREIGN KEY (maintenance_head_id) REFERENCES maintenance_heads(id),
    INDEX idx_bill (bill_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id              INT UNSIGNED  NOT NULL,
    bill_id                 INT UNSIGNED  NOT NULL,
    flat_id                 INT UNSIGNED  NOT NULL,
    receipt_number          VARCHAR(50)   NOT NULL UNIQUE,
    amount_received         DECIMAL(10,2) NOT NULL,
    payment_method          ENUM('cash','upi','bank_transfer','cheque','card','other') NOT NULL,
    transaction_reference   VARCHAR(100)  NULL,
    payment_date            DATE          NOT NULL,
    received_by             INT UNSIGNED  NOT NULL,
    remarks                 TEXT          NULL,
    status                  ENUM('confirmed','cancelled','bounced') DEFAULT 'confirmed',
    receipt_pdf_path        VARCHAR(255)  NULL,
    created_at              DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id)  REFERENCES societies(id)       ON DELETE CASCADE,
    FOREIGN KEY (bill_id)     REFERENCES bills(id),
    FOREIGN KEY (flat_id)     REFERENCES flats(id),
    FOREIGN KEY (received_by) REFERENCES society_admins(id),
    INDEX idx_bill          (bill_id),
    INDEX idx_society_date  (society_id, payment_date),
    INDEX idx_flat          (flat_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_history (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id      INT UNSIGNED NOT NULL,
    field_changed   VARCHAR(50)  NOT NULL,
    old_value       TEXT         NULL,
    new_value       TEXT         NULL,
    changed_by      INT UNSIGNED NOT NULL,
    change_reason   TEXT         NULL,
    changed_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(id)        ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES society_admins(id),
    INDEX idx_payment (payment_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS otp_codes (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_type           ENUM('super_admin','society_admin','resident') NOT NULL,
    identifier          VARCHAR(150) NOT NULL,
    identifier_type     ENUM('mobile','email') NOT NULL,
    otp_code            VARCHAR(64)  NOT NULL,
    purpose             ENUM('login','signup','reset_password','verify') DEFAULT 'login',
    attempts            TINYINT      DEFAULT 0,
    is_used             TINYINT(1)   DEFAULT 0,
    expires_at          DATETIME     NOT NULL,
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_identifier (identifier, identifier_type),
    INDEX idx_expires    (expires_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id      INT UNSIGNED NULL,
    user_type       ENUM('super_admin','society_admin','resident','system') NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    user_name       VARCHAR(100) NOT NULL,
    action          VARCHAR(100) NOT NULL,
    entity_type     VARCHAR(50)  NULL,
    entity_id       INT UNSIGNED NULL,
    old_values      JSON         NULL,
    new_values      JSON         NULL,
    ip_address      VARCHAR(45)  NULL,
    user_agent      TEXT         NULL,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_society_date (society_id, created_at),
    INDEX idx_entity       (entity_type, entity_id),
    INDEX idx_user         (user_type, user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS email_logs (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id              INT UNSIGNED NULL,
    recipient_email         VARCHAR(150) NOT NULL,
    recipient_type          ENUM('super_admin','society_admin','resident') NOT NULL,
    recipient_id            INT UNSIGNED NULL,
    subject                 VARCHAR(255) NOT NULL,
    body                    TEXT         NOT NULL,
    template                VARCHAR(100) NULL,
    related_entity_type     VARCHAR(50)  NULL,
    related_entity_id       INT UNSIGNED NULL,
    status                  ENUM('queued','sent','failed','bounced') DEFAULT 'queued',
    error_message           TEXT         NULL,
    sent_at                 DATETIME     NULL,
    created_at              DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status    (status),
    INDEX idx_recipient (recipient_type, recipient_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expenses (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id              INT UNSIGNED  NOT NULL,
    expense_date            DATE          NOT NULL,
    category                VARCHAR(100)  NOT NULL,
    description             VARCHAR(500)  NOT NULL,
    amount                  DECIMAL(10,2) NOT NULL,
    paid_to                 VARCHAR(150)  NULL,
    payment_method          ENUM('cash','upi','bank_transfer','cheque','other') NOT NULL,
    transaction_reference   VARCHAR(100)  NULL,
    receipt_attachment      VARCHAR(255)  NULL,
    recorded_by             INT UNSIGNED  NOT NULL,
    created_at              DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id)  REFERENCES societies(id)       ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES society_admins(id),
    INDEX idx_society_date (society_id, expense_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notices (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id          INT UNSIGNED NOT NULL,
    title               VARCHAR(255) NOT NULL,
    content             TEXT         NOT NULL,
    priority            ENUM('low','normal','high','urgent') DEFAULT 'normal',
    target_audience     ENUM('all','owners','tenants','specific_block') DEFAULT 'all',
    target_block_id     INT UNSIGNED NULL,
    valid_from          DATE         NULL,
    valid_until         DATE         NULL,
    posted_by           INT UNSIGNED NOT NULL,
    is_active           TINYINT(1)   DEFAULT 1,
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id)      REFERENCES societies(id)       ON DELETE CASCADE,
    FOREIGN KEY (target_block_id) REFERENCES blocks(id)          ON DELETE SET NULL,
    FOREIGN KEY (posted_by)       REFERENCES society_admins(id),
    INDEX idx_society_active (society_id, is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS complaints (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    society_id          INT UNSIGNED NOT NULL,
    flat_id             INT UNSIGNED NOT NULL,
    resident_id         INT UNSIGNED NOT NULL,
    ticket_number       VARCHAR(50)  NOT NULL UNIQUE,
    category            VARCHAR(100) NOT NULL,
    subject             VARCHAR(255) NOT NULL,
    description         TEXT         NOT NULL,
    priority            ENUM('low','normal','high','urgent') DEFAULT 'normal',
    status              ENUM('open','in_progress','resolved','closed','rejected') DEFAULT 'open',
    assigned_to         INT UNSIGNED NULL,
    resolved_at         DATETIME     NULL,
    resolution_notes    TEXT         NULL,
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (society_id)  REFERENCES societies(id)       ON DELETE CASCADE,
    FOREIGN KEY (flat_id)     REFERENCES flats(id),
    FOREIGN KEY (resident_id) REFERENCES residents(id),
    FOREIGN KEY (assigned_to) REFERENCES society_admins(id) ON DELETE SET NULL,
    INDEX idx_society_status (society_id, status)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;