CREATE TABLE leases (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    renter_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    lease_number VARCHAR(50) UNIQUE NOT NULL,
    lease_type VARCHAR(50) CHECK (lease_type IN (
        'fixed_term', 'month_to_month', 'quarterly', 'annual', 'commercial'
    )),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    actual_end_date DATE,
    move_in_date DATE,
    notice_period_days INTEGER DEFAULT 30,
    monthly_rent DECIMAL(10, 2) NOT NULL,
    security_deposit DECIMAL(10, 2) NOT NULL,
    security_deposit_type VARCHAR(50) DEFAULT 'fixed' CHECK (security_deposit_type IN (
        'fixed', 'months_rent'
    )),
    security_deposit_months INTEGER DEFAULT 1,
    pet_deposit DECIMAL(10, 2) DEFAULT 0,
    pet_rent DECIMAL(10, 2) DEFAULT 0,
    parking_fee DECIMAL(10, 2) DEFAULT 0,
    utility_fee DECIMAL(10, 2) DEFAULT 0,
    late_fee_percentage DECIMAL(5, 2) DEFAULT 5.00,
    late_fee_grace_period INTEGER DEFAULT 5,
    rent_due_day INTEGER DEFAULT 1,
    rent_frequency VARCHAR(50) DEFAULT 'monthly' CHECK (rent_frequency IN (
        'monthly', 'quarterly', 'annually'
    )),
    discount_percentage DECIMAL(5, 2) DEFAULT 0,
    discount_condition TEXT,
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN (
        'draft', 'pending_approval', 'active', 'renewed', 'terminated', 
        'expired', 'cancelled', 'pending_activation'
    )),
    renewal_number INTEGER DEFAULT 0,
    parent_lease_id UUID REFERENCES leases(id),
    renewed_to_id UUID REFERENCES leases(id),
    termination_reason TEXT,
    termination_fee DECIMAL(10, 2) DEFAULT 0,
    termination_fee_paid BOOLEAN DEFAULT FALSE,
    terms JSONB DEFAULT '{}',
    special_conditions TEXT,
    pets_allowed BOOLEAN DEFAULT FALSE,
    max_occupants INTEGER DEFAULT 1,
    signed_lease_document_url VARCHAR(500),
    signed_by_tenant BOOLEAN DEFAULT FALSE,
    signed_by_landlord BOOLEAN DEFAULT FALSE,
    signed_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);
CREATE TABLE lease_renewals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    lease_id UUID NOT NULL REFERENCES leases(id) ON DELETE CASCADE,
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    previous_rent DECIMAL(10, 2) NOT NULL,
    new_rent DECIMAL(10, 2) NOT NULL,
    rent_increase_percentage DECIMAL(5, 2),
    previous_end_date DATE NOT NULL,
    new_end_date DATE NOT NULL,
    status VARCHAR(50) DEFAULT 'offered' CHECK (status IN (
        'offered', 'accepted', 'rejected', 'expired', 'negotiating'
    )),
    tenant_response VARCHAR(50),
    tenant_response_date TIMESTAMP WITH TIME ZONE,
    negotiation_notes TEXT,
    new_terms JSONB DEFAULT '{}',
    offered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    accepted_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);
CREATE TABLE lease_documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    lease_id UUID NOT NULL REFERENCES leases(id) ON DELETE CASCADE,
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    document_type VARCHAR(100) CHECK (document_type IN (
        'lease_agreement', 'addendum', 'renewal_letter', 'termination_notice',
        'inspection_report', 'move_in_checklist', 'move_out_checklist', 'other'
    )),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    file_name VARCHAR(255) NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    file_size INTEGER,
    requires_signature BOOLEAN DEFAULT FALSE,
    signed BOOLEAN DEFAULT FALSE,
    signed_at TIMESTAMP WITH TIME ZONE,
    signed_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    uploaded_by UUID REFERENCES users(id)
);
CREATE INDEX idx_leases_org ON leases(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_leases_property ON leases(property_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_leases_unit ON leases(unit_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_leases_renter ON leases(renter_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_leases_status ON leases(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_leases_dates ON leases(start_date, end_date) WHERE deleted_at IS NULL;
CREATE INDEX idx_leases_number ON leases(lease_number);
CREATE INDEX idx_leases_parent ON leases(parent_lease_id);
CREATE INDEX idx_leases_renewed ON leases(renewed_to_id);
CREATE INDEX idx_lease_renewals_lease ON lease_renewals(lease_id);
CREATE INDEX idx_lease_renewals_status ON lease_renewals(status);
CREATE INDEX idx_lease_renewals_org ON lease_renewals(organization_tenant_id);
CREATE INDEX idx_lease_docs_lease ON lease_documents(lease_id);
CREATE INDEX idx_lease_docs_type ON lease_documents(document_type);
CREATE INDEX idx_lease_docs_org ON lease_documents(organization_tenant_id);
CREATE TRIGGER update_leases_timestamp BEFORE UPDATE ON leases
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_lease_renewals_timestamp BEFORE UPDATE ON lease_renewals
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_lease_docs_timestamp BEFORE UPDATE ON lease_documents
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();