-- Just copy and paste this into Supabase SQL Editor and click RUN

CREATE TABLE IF NOT EXISTS orders (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id TEXT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status TEXT DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS order_items (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_name TEXT,
    quantity INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL
);

-- Audits synced from the Android app (AuditRepository -> rest/v1/audits).
-- NOTE: id is TEXT because the app generates its own IDs (e.g. "audit_...").
CREATE TABLE IF NOT EXISTS audits (
    id TEXT PRIMARY KEY,
    hotel_id TEXT NOT NULL,
    inspector_name TEXT,
    inspector_role TEXT,
    department_name TEXT,
    compliance_score DECIMAL(5,2),
    status TEXT DEFAULT 'COMPLETED',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Hazards reported from the Android app (rest/v1/hazards).
CREATE TABLE IF NOT EXISTS hazards (
    id TEXT PRIMARY KEY,
    hotel_id TEXT NOT NULL,
    department_name TEXT,
    zone TEXT,
    description TEXT,
    priority TEXT,
    reporter_name TEXT,
    status TEXT DEFAULT 'PENDING'
);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE audits ENABLE ROW LEVEL SECURITY;
ALTER TABLE hazards ENABLE ROW LEVEL SECURITY;

-- WARNING: these policies allow anonymous access because the mobile app uses
-- the Supabase anon key without user authentication. Fine for a demo;
-- before production, replace with auth-scoped policies.
CREATE POLICY "Allow all" ON orders FOR ALL USING (true);
CREATE POLICY "Allow all" ON order_items FOR ALL USING (true);
CREATE POLICY "Allow all" ON audits FOR ALL USING (true);
CREATE POLICY "Allow all" ON hazards FOR ALL USING (true);

SELECT '✅ Database ready!' as status;