-- =============================================================================
-- BeKind Backend — Resident Contracts Schema (Structured like resident_documents)
-- Tables: contracts, contract_files
-- Purpose: Store contracts per resident with verification and file attachments
-- Depends on: 001-auth-schema.sql (users, update_updated_at_column)
--             005-resident-and-facility-schema.sql (residents)
--             002-file-schema.sql (files)
-- =============================================================================

-- =============================================================================
-- EXTENSIONS
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =============================================================================
-- ENUM TYPES
-- =============================================================================

-- Contract type: Loại HĐ
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'contract_type') THEN
        CREATE TYPE contract_type AS ENUM (
            'residential_lease',     -- Thuê nhà/căn hộ
            'room_rental',           -- Thuê phòng
            'service_agreement',     -- Hợp đồng dịch vụ
            'other'                  -- Khác
        );
    END IF;
END $$;

-- =============================================================================
-- TABLE: contracts (Hợp Đồng)
-- Similar structure to resident_documents
-- One contract per resident, with metadata and verification fields
-- =============================================================================

CREATE TABLE IF NOT EXISTS contracts (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Resident link (FK to residents)
    resident_id UUID NOT NULL REFERENCES residents(id) ON DELETE CASCADE,

    -- Contract metadata
    contract_number VARCHAR(100) NOT NULL,                          -- Mã HĐ
    contract_name VARCHAR(255),                                     -- Tên HĐ
    contract_type contract_type NOT NULL,                           -- Loại HĐ  -- ENUM: residential_lease, room_rental, service_agreement, other
    description TEXT,                                               -- Mô tả

    -- Financial info
    rent_amount DECIMAL(15, 2),                                     -- Giá thuê
    deposit_amount DECIMAL(15, 2),                                  -- Tiền coc
    payment_day INTEGER,                                            -- Chu kỳ TT (day 1-31)

    -- Date fields
    signed_date DATE,                                               -- Ngày ký
    expiry_date DATE,                                               -- Ngày hết hạn

    -- Primary & Verification
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,                      -- Is primary contract for resident
    verified_at TIMESTAMP WITH TIME ZONE,                           -- Ngày xác thực
    verified_by UUID REFERENCES users(id) ON DELETE SET NULL,       -- Xác thực bởi

    -- Audit & Soft delete
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT contracts_number_not_empty CHECK (length(trim(contract_number)) > 0),
    CONSTRAINT contracts_rent_positive CHECK (rent_amount IS NULL OR rent_amount > 0),
    CONSTRAINT contracts_deposit_positive CHECK (deposit_amount IS NULL OR deposit_amount > 0),
    CONSTRAINT contracts_payment_day_valid CHECK (payment_day IS NULL OR (payment_day >= 1 AND payment_day <= 31)),
    CONSTRAINT contracts_expiry_after_signed CHECK (expiry_date IS NULL OR signed_date IS NULL OR expiry_date >= signed_date),
    CONSTRAINT contracts_verification_consistency CHECK (
        (verified_at IS NULL AND verified_by IS NULL)
        OR (verified_at IS NOT NULL)
    ),
    CONSTRAINT contracts_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: contract_files (Tệp Hợp Đồng - Attachments)
-- Stores file attachments for contracts (like resident_documents has files)
-- =============================================================================

CREATE TABLE IF NOT EXISTS contract_files (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Links
    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
    file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,

    -- Flags
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,                      -- Is primary file for this contract

    -- Audit & Soft delete
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT contract_files_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- INDEXES
-- =============================================================================

-- Contracts indexes
CREATE INDEX IF NOT EXISTS idx_contracts_resident_id ON contracts(resident_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_contracts_number ON contracts(contract_number) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_contracts_type ON contracts(contract_type) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_contracts_resident_number ON contracts(resident_id, contract_number) WHERE deleted_at IS NULL;

-- Contract Files indexes
CREATE INDEX IF NOT EXISTS idx_contract_files_contract_id ON contract_files(contract_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_contract_files_file_id ON contract_files(file_id);

-- =============================================================================
-- QUERY EXAMPLES
-- =============================================================================

-- Get all contracts for a resident:
-- SELECT * FROM contracts_with_resident_info
-- WHERE resident_id = ? AND deleted_at IS NULL;

-- Get primary contract for resident:
-- SELECT * FROM contracts_with_resident_info
-- WHERE resident_id = ? AND deleted_at IS NULL;

-- Get contract with files:
-- SELECT c.*, f.* FROM contracts c
-- LEFT JOIN contract_files cf ON cf.contract_id = c.id AND cf.deleted_at IS NULL
-- LEFT JOIN files f ON f.id = cf.file_id
-- WHERE c.id = ? AND c.deleted_at IS NULL;

-- Get all contracts expiring soon:
-- SELECT * FROM contracts_with_resident_info
-- WHERE expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
-- AND deleted_at IS NULL
-- ORDER BY expiry_date;

-- Get unverified contracts:
-- SELECT * FROM contracts_with_resident_info
-- WHERE verified_at IS NULL AND deleted_at IS NULL;

-- =============================================================================
-- ROLLBACK (if needed)
-- =============================================================================
-- DROP VIEW IF EXISTS contracts_with_resident_info;
-- DROP TABLE IF EXISTS contract_files;
-- DROP TABLE IF EXISTS contracts;
-- DROP TYPE IF EXISTS contract_type;
-- ============================================================================
