-- 1. Extensions
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgvector;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- 2. Schema Setup
CREATE TABLE IF NOT EXISTS districts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name_ar TEXT NOT NULL,
    name_en TEXT NOT NULL,
    boundary GEOMETRY(MultiPolygon, 4326),
    city_name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS micro_grids (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    district_id UUID REFERENCES districts(id),
    grid_geom GEOMETRY(Polygon, 4326),
    avg_price_sqm DECIMAL(12, 2) DEFAULT 0,
    liquidity_score FLOAT DEFAULT 0,
    last_updated TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    district_id UUID REFERENCES districts(id),
    micro_grid_id UUID REFERENCES micro_grids(id),
    location GEOMETRY(Point, 4326),
    street_width_m FLOAT,
    facade_direction TEXT, -- North, East, South, West
    land_area_sqm FLOAT NOT NULL,
    construction_age_years INT DEFAULT 0,
    is_commercial BOOLEAN DEFAULT false,
    owner_id UUID DEFAULT auth.uid(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID REFERENCES properties(id),
    transaction_date DATE NOT NULL,
    price_total DECIMAL(15, 2) NOT NULL,
    price_sqm DECIMAL(12, 2),
    is_verified_moj BOOLEAN DEFAULT false,
    confidence_score FLOAT DEFAULT 1.0, -- MOJ=1.0, Ejar=0.9, Scraper=0.6
    transaction_hash TEXT UNIQUE,
    flags TEXT[] DEFAULT ARRAY[]::TEXT[],
    metadata JSONB DEFAULT '{}'::jsonb
);

-- 4. Filters & Market Integrity (The Median Logic)
CREATE OR REPLACE FUNCTION update_grid_median_valuation()
RETURNS TRIGGER AS $$
DECLARE
    property_grid_id UUID;
    median_price DECIMAL;
BEGIN
    -- 1. Precision Median Engine (Applying 100% Reality Check)
    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY (price_total / NULLIF((SELECT land_area_sqm FROM properties WHERE id = p.id), 0)))
    INTO weighted_median
    FROM transactions t
    JOIN properties p ON t.property_id = p.id
    WHERE p.micro_grid_id = property_grid_id
    AND t.transaction_date > NOW() - INTERVAL '180 days'
    AND t.confidence_score >= 0.9; -- Strict Threshold for "True Price"

    UPDATE micro_grids 
    SET avg_price_sqm = weighted_median,
        last_updated = NOW()
    WHERE id = property_grid_id;

    -- 2. Intra-Family/Gift Filter (Median-Based)
    IF weighted_median IS NOT NULL AND (NEW.price_total / (SELECT land_area_sqm FROM properties WHERE id = NEW.property_id)) < (weighted_median * 0.4) THEN
        NEW.flags = array_append(NEW.flags, 'GIFT_TRANSACTION');
    END IF;

    -- 3. Advanced Monopoly Detection (Sovereign Level)
    IF (NEW.metadata->>'client_id') IS NOT NULL AND (
        SELECT count(*) FROM transactions t
        JOIN properties p ON t.property_id = p.id
        WHERE p.micro_grid_id = property_grid_id
        AND t.metadata->>'client_id' = NEW.metadata->>'client_id'
        AND t.transaction_date > NOW() - INTERVAL '90 days'
    ) > 3 THEN
        NEW.flags = array_append(NEW.flags, 'MONOPOLISTIC_BUYOUT');
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER transaction_median_update
    BEFORE INSERT ON transactions
    FOR EACH ROW
    EXECUTE FUNCTION update_grid_median_valuation();

-- 5. Row Level Security
ALTER TABLE districts ENABLE ROW LEVEL SECURITY;
ALTER TABLE properties ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public read for analytics" ON transactions FOR SELECT USING (true);
CREATE POLICY "Owner crud for properties" ON properties FOR ALL USING (auth.uid() = owner_id);