Portal Warga CGV Database Schema

-- Neighborhood Community Core Schema
CREATE TABLE households (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    block_number VARCHAR(10) NOT NULL,
    house_number VARCHAR(10) NOT NULL,
    resident_count INT DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

CREATE TABLE residents (
    id UUID REFERENCES auth.users PRIMARY KEY,
    household_id UUID REFERENCES households(id) ON DELETE SET NULL,
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20),
    role VARCHAR(20) DEFAULT 'resident' CHECK (role IN ('admin', 'resident', 'security', 'treasurer')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

CREATE TABLE billing_ledger (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    household_id UUID REFERENCES households(id) ON DELETE CASCADE,
    billing_period DATE NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'unpaid' CHECK (status IN ('unpaid', 'pending_verification', 'paid')),
    payment_method VARCHAR(30),
    transaction_ref VARCHAR(100),
    verified_by UUID REFERENCES residents(id),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);