-- Sovereign Real Estate Database Schema (Phase 7)
-- Target: Supabase / PostgreSQL

CREATE TABLE IF NOT EXISTS market_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    district TEXT NOT NULL,
    total_price NUMERIC NOT NULL,
    area_sqm NUMERIC NOT NULL,
    price_per_meter NUMERIC GENERATED ALWAYS AS (total_price / NULLIF(area_sqm, 0)) STORED,
    transaction_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    property_type TEXT CHECK (property_type IN ('Villa', 'Apartment', 'Land')),
    source TEXT DEFAULT 'MOJ',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indices for high-precision real-time lookups
CREATE INDEX IF NOT EXISTS idx_district_type ON market_transactions (district, property_type);
CREATE INDEX IF NOT EXISTS idx_transaction_date ON market_transactions (transaction_date DESC);

-- Example Insertion
-- INSERT INTO market_transactions (district, total_price, area_sqm, property_type)
-- VALUES ('Al Malqa', 4500000, 750, 'Villa');