Below is the complete, production-ready PostgreSQL setup script to construct schemas, relational entities, catalog coverages, laboratory inventories, Row Level Security constraints, triggers, and compatibility views automatically:

-- =========================================================================
--                     DEVELOPER SETUP SCRIPT
-- =========================================================================
-- WARNING: Do not run this on a production database without reviewing
-- DROP statements first, as they will delete existing tables and data!
-- =========================================================================

-- Optional: Clean up existing schema (active by default to prevent type mismatch on upgrade)
DROP VIEW IF EXISTS public.profiles CASCADE;
DROP TABLE IF EXISTS public.alerts CASCADE;
DROP TABLE IF EXISTS public.orders CASCADE;
DROP TABLE IF EXISTS public.repair_parts_inventory CASCADE;
DROP TABLE IF EXISTS public.parts_pricing CASCADE;
DROP TABLE IF EXISTS public.parts CASCADE;
DROP TABLE IF EXISTS public.repair_types CASCADE;
DROP TABLE IF EXISTS public.devices CASCADE;
DROP TABLE IF EXISTS public.brands CASCADE;
DROP TABLE IF EXISTS public.user_roles CASCADE;
DROP TABLE IF EXISTS public.roles CASCADE;
DROP TABLE IF EXISTS public.users CASCADE;

-- 1. Create Users Table (user schema mirroring)
CREATE TABLE IF NOT EXISTS public.users (
    id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT,
    phone TEXT,
    address TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 2. Create Roles Master Reference
CREATE TABLE IF NOT EXISTS public.roles (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

INSERT INTO public.roles (id, name) VALUES
(1, 'admin'), (2, 'coordinator'), (3, 'technician'), (4, 'repairmaster'), (5, 'customer')
ON CONFLICT (id) DO NOTHING;

-- 3. Create user_roles junction
CREATE TABLE IF NOT EXISTS public.user_roles (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    role_id INT REFERENCES public.roles(id) ON DELETE CASCADE,
    UNIQUE(user_id, role_id)
);

-- 4. Create Brands Table
CREATE TABLE IF NOT EXISTS public.brands (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

INSERT INTO public.brands (id, name) VALUES
('b1', 'Apple'), ('b2', 'Samsung'), ('b3', 'Vivo'), ('b4', 'OnePlus'), 
('b5', 'Xiaomi (MI)'), ('b6', 'Oppo'), ('b7', 'Realme'), ('b8', 'Google'), 
('b9', 'Nothing'), ('b10', 'Motorola'), ('b11', 'iQOO'), ('b12', 'Lava')
ON CONFLICT (id) DO NOTHING;

-- 5. Create Devices Table
CREATE TABLE IF NOT EXISTS public.devices (
    id TEXT PRIMARY KEY,
    brand_id TEXT REFERENCES public.brands(id) ON DELETE CASCADE,
    name TEXT NOT NULL
);

INSERT INTO public.devices (id, brand_id, name) VALUES
('d1', 'b1', 'iPhone 15 Pro Max'),
('d2', 'b1', 'iPhone 15'),
('d3', 'b3', 'Vivo V30 Pro'),
('d4', 'b3', 'Vivo V29 Pro'),
('d5', 'b2', 'Galaxy S24 Ultra'),
('d6', 'b2', 'Galaxy A55'),
('d7', 'b4', 'OnePlus 12'),
('d8', 'b5', 'Redmi Note 13 Pro'),
('d9', 'b6', 'Reno 11 Pro'),
('d10', 'b7', 'GT 6 Pro'),
('d11', 'b8', 'Pixel 8 Pro'),
('d12', 'b9', 'Phone 2'),
('d13', 'b10', 'Edge 50 Pro'),
('d14', 'b11', 'iQOO 12'),
('d15', 'b12', 'Agni 2')
ON CONFLICT (id) DO NOTHING;

-- 6. Create Repair Types Reference
CREATE TABLE IF NOT EXISTS public.repair_types (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    label TEXT NOT NULL
);

INSERT INTO public.repair_types (id, name, label) VALUES
('rt1', 'screen', '📱 Screen Replacement'),
('rt2', 'battery', '🔋 Battery Replacement'),
('rt3', 'chargingport', '🔌 Charging Port Repair'),
('rt4', 'camera', '📷 Camera Repair'),
('rt5', 'speaker', '🔊 Speaker / Mic Repair'),
('rt6', 'button', '🔘 Button Repair'),
('rt7', 'motherboard', '💻 Motherboard Repair'),
('rt8', 'waterdamage', '💧 Water Damage Repair'),
('rt9', 'software', '📀 Software / OS Repair'),
('rt10', 'network', '📶 Network / Antenna Repair'),
('rt11', 'completeoverhaul', '⚙️ Complete Overhaul')
ON CONFLICT (id) DO NOTHING;

-- 7. Create Parts Table for catalog lookup
CREATE TABLE IF NOT EXISTS public.parts (
    id SERIAL PRIMARY KEY,
    device_id TEXT REFERENCES public.devices(id) ON DELETE CASCADE,
    repair_type_id TEXT REFERENCES public.repair_types(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

INSERT INTO public.parts (device_id, repair_type_id, name, price) VALUES
('d3', 'rt1', 'AMOLED Screen Panel Assembly', 6300.00),
('d3', 'rt1', 'Digitizer & Display Flex', 504.00),
('d3', 'rt1', 'Water-Resistant Frame Adhesive Seal', 126.00),
('d3', 'rt2', 'Certified Li-Po 5000mAh Battery Cell', 1500.00),
('d3', 'rt2', 'Thermal Dissipation Pad', 150.00),
('d1', 'rt1', 'Super Retina XDR OLED Display', 25200.00),
('d1', 'rt1', 'Force Touch Digitizer Sensor', 2016.00),
('d1', 'rt1', 'IP68 Watertight Perimeter Seal Adhesive', 504.00),
('d1', 'rt2', 'OEM Battery Cell Replacement', 4500.00),
('d5', 'rt1', 'Dynamic AMOLED 2X Display Module', 21500.00),
('d5', 'rt1', 'Corning Gorilla Armor Glass Layer', 3200.00),
('d5', 'rt2', 'Official Li-Ion 5000mAh Battery Pack', 2800.00),
('d5', 'rt3', 'USB-C SuperFast Charging Port PCB', 1400.00),
('d5', 'rt7', 'Logic Board IC Power Management Chip', 5500.00)
ON CONFLICT DO NOTHING;

-- 8. Create Parts Pricing Table (For Quick Calculator Tool)
CREATE TABLE IF NOT EXISTS public.parts_pricing (
    id SERIAL PRIMARY KEY,
    brand TEXT NOT NULL,
    model TEXT NOT NULL,
    issue_type TEXT NOT NULL,
    part_name TEXT NOT NULL,
    tier TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    labor DECIMAL(10,2) NOT NULL,
    compatible_models TEXT,
    warranty TEXT,
    turnaround TEXT,
    source_hub TEXT,
    wholesale_price DECIMAL(10,2),
    wholesale_source TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

INSERT INTO public.parts_pricing (brand, model, issue_type, part_name, tier, price, labor, compatible_models, warranty, turnaround, source_hub, wholesale_price, wholesale_source) VALUES
('Apple', 'iPhone 15 Pro Max', 'Screen Replacement', '[Premium] Superb OEM-equivalent component for iPhone 15 Pro Max', 'Premium', 14500.00, 1500.00, 'iPhone 15 Pro', '180 Days Direct Hub Warranty', '1-2 Hours', 'Wardha Central Hub', 9500.00, 'Telipura Market, Sitabuldi, Nagpur'),
('Apple', 'iPhone 15 Pro Max', 'Screen Replacement', '[Standard] High-grade certified third-party component for iPhone 15 Pro Max', 'Standard', 8400.00, 1250.00, 'iPhone 15 Pro', '90 Days Local Warranty', '1-2 Hours', 'Nagpur Local Hub', 5500.00, 'Gaffar Market, Karol Bagh, Delhi'),
('Apple', 'iPhone 15 Pro Max', 'Screen Replacement', '[Compatible] Cost-effective functional component for iPhone 15 Pro Max', 'Compatible', 4600.00, 1100.00, 'iPhone 15 Pro', '30 Days Basic Warranty', '1-2 Hours', 'Mumbai Hub', 3000.00, 'Manish Market, Fort, Mumbai'),
('Apple', 'iPhone 15 Pro Max', 'Battery Replacement', '[Premium] Superb OEM-equivalent component for iPhone 15 Pro Max', 'Premium', 3200.00, 800.00, 'iPhone 15 Pro', '180 Days Direct Hub Warranty', '30-45 Mins', 'Wardha Central Hub', 2100.00, 'SP Road, Halasuru, Bengaluru'),
('Vivo', 'Vivo V30 Pro', 'Screen Replacement', '[Premium] Superb OEM-equivalent component for Vivo V30 Pro', 'Premium', 5800.00, 1000.00, 'Vivo V30', '180 Days Direct Hub Warranty', '1-2 Hours', 'Nagpur Local Hub', 3800.00, 'Telipura Market, Sitabuldi, Nagpur'),
('Vivo', 'Vivo V30 Pro', 'Charging Port Repair', '[Standard] High-grade certified third-party component for Vivo V30 Pro', 'Standard', 850.00, 400.00, 'Vivo V30', '90 Days Local Warranty', '30 Mins', 'Nagpur Local Hub', 550.00, 'Modi No. 3, Sitabuldi, Nagpur')
ON CONFLICT DO NOTHING;

-- 9. Create Repair Parts Inventory Table (For Lab Stock)
CREATE TABLE IF NOT EXISTS public.repair_parts_inventory (
    id SERIAL PRIMARY KEY,
    part_name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Seed some laboratory stock
INSERT INTO public.repair_parts_inventory (part_name, price, quantity) VALUES
('OEM AMOLED Display Panel Assembly', 6200.00, 12),
('High-Density Li-Po Battery Core (Vivo V30 Pro)', 1350.00, 25),
('USB-C Charging Board (Sub-PCB)', 650.00, 40),
('IP68 Perimeter Liquid Seal Adhesive Adhesive Strip', 95.00, 100),
('Super Retina OLED Panel Assembly (iPhone 15 Pro Max)', 23500.00, 4),
('Ultra-Premium Thermal Paste Syringe', 450.00, 15)
ON CONFLICT DO NOTHING;

-- 10. Create Orders Table
CREATE TABLE IF NOT EXISTS public.orders (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    order_number TEXT UNIQUE NOT NULL,
    user_id UUID REFERENCES auth.users(id),
    customer_name TEXT NOT NULL,
    customer_phone TEXT NOT NULL,
    customer_email TEXT,
    address TEXT NOT NULL,
    device_id TEXT,
    device_other TEXT,
    repair_type_id TEXT,
    repair_other TEXT,
    photo_url TEXT,
    additional_notes TEXT,
    status TEXT DEFAULT 'Pending' NOT NULL,
    parts_quality TEXT,
    parts_total DECIMAL(10,2) DEFAULT 0,
    service_fee DECIMAL(10,2) DEFAULT 0,
    diagnosis_charge DECIMAL(10,2) DEFAULT 250,
    total_price DECIMAL(10,2) DEFAULT 0,
    discount_applied DECIMAL(10,2) DEFAULT 0,
    custom_quote_parts TEXT,
    diagnosis_notes TEXT,
    notes TEXT, -- Advice to Coordinator
    pickup_otp TEXT,
    assigned_to UUID REFERENCES auth.users(id),
    technician_id UUID REFERENCES auth.users(id),
    repairmaster_id UUID REFERENCES auth.users(id),
    customer_rating INT,
    customer_review TEXT,
    invoice_number TEXT,
    payment_method TEXT,
    payment_status TEXT DEFAULT 'Unpaid',
    tax_amount DECIMAL(10,2) DEFAULT 0,
    platform_fee DECIMAL(10,2) DEFAULT 0,
    grand_total DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 11. Create Coordinator Alerts Table
CREATE TABLE IF NOT EXISTS public.alerts (
    id SERIAL PRIMARY KEY,
    order_id UUID REFERENCES public.orders(id) ON DELETE CASCADE,
    message TEXT NOT NULL,
    type TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 12. Enable Row Level Security (RLS)
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.brands ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.repair_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.parts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.parts_pricing ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.repair_parts_inventory ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.alerts ENABLE ROW LEVEL SECURITY;

-- 13. Create Security Policies
DROP POLICY IF EXISTS "Allow public select" ON public.users;
DROP POLICY IF EXISTS "Allow authenticated insert" ON public.users;
DROP POLICY IF EXISTS "Allow user self update" ON public.users;
CREATE POLICY "Allow public select" ON public.users FOR SELECT USING (true);
CREATE POLICY "Allow authenticated insert" ON public.users FOR INSERT WITH CHECK (auth.uid() = id);
CREATE POLICY "Allow user self update" ON public.users FOR UPDATE USING (auth.uid() = id);

DROP POLICY IF EXISTS "Allow public select roles" ON public.roles;
CREATE POLICY "Allow public select roles" ON public.roles FOR SELECT USING (true);

DROP POLICY IF EXISTS "Allow public select user_roles" ON public.user_roles;
DROP POLICY IF EXISTS "Allow authenticated operations user_roles" ON public.user_roles;
CREATE POLICY "Allow public select user_roles" ON public.user_roles FOR SELECT USING (true);
CREATE POLICY "Allow authenticated operations user_roles" ON public.user_roles FOR ALL USING (true);

DROP POLICY IF EXISTS "Allow public select brands" ON public.brands;
CREATE POLICY "Allow public select brands" ON public.brands FOR SELECT USING (true);

DROP POLICY IF EXISTS "Allow public select devices" ON public.devices;
CREATE POLICY "Allow public select devices" ON public.devices FOR SELECT USING (true);

DROP POLICY IF EXISTS "Allow public select repair_types" ON public.repair_types;
CREATE POLICY "Allow public select repair_types" ON public.repair_types FOR SELECT USING (true);

DROP POLICY IF EXISTS "Allow public select parts" ON public.parts;
CREATE POLICY "Allow public select parts" ON public.parts FOR SELECT USING (true);

DROP POLICY IF EXISTS "Allow public select parts_pricing" ON public.parts_pricing;
CREATE POLICY "Allow public select parts_pricing" ON public.parts_pricing FOR SELECT USING (true);

DROP POLICY IF EXISTS "Allow public select inventory" ON public.repair_parts_inventory;
DROP POLICY IF EXISTS "Allow authenticated operations inventory" ON public.repair_parts_inventory;
CREATE POLICY "Allow public select inventory" ON public.repair_parts_inventory FOR SELECT USING (true);
CREATE POLICY "Allow authenticated operations inventory" ON public.repair_parts_inventory FOR ALL USING (true);

DROP POLICY IF EXISTS "Allow public select orders" ON public.orders;
DROP POLICY IF EXISTS "Allow authenticated operations" ON public.orders;
CREATE POLICY "Allow public select orders" ON public.orders FOR SELECT USING (true);
CREATE POLICY "Allow authenticated operations" ON public.orders FOR ALL USING (true);

DROP POLICY IF EXISTS "Allow public select alerts" ON public.alerts;
DROP POLICY IF EXISTS "Allow authenticated operations alerts" ON public.alerts;
CREATE POLICY "Allow public select alerts" ON public.alerts FOR SELECT USING (true);
CREATE POLICY "Allow authenticated operations alerts" ON public.alerts FOR ALL USING (true);

-- 14. Auto-mirror new Auth users to Public Users and User Roles
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, email, name, phone, address)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'full_name', ''),
    COALESCE(NEW.raw_user_meta_data->>'phone', ''),
    COALESCE(NEW.raw_user_meta_data->>'city', 'Wardha')
  )
  ON CONFLICT (id) DO UPDATE SET
    email = EXCLUDED.email,
    name = COALESCE(EXCLUDED.name, public.users.name),
    phone = COALESCE(EXCLUDED.phone, public.users.phone);

  INSERT INTO public.user_roles (user_id, role_id)
  VALUES (NEW.id, 5) -- Default Role is 'customer'
  ON CONFLICT (user_id, role_id) DO NOTHING;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- 15. Create Profiles View for frontend backward compatibility
CREATE OR REPLACE VIEW public.profiles AS
WITH user_highest_role AS (
    SELECT DISTINCT ON (ur.user_id)
        ur.user_id,
        r.name AS role_name
    FROM public.user_roles ur
    JOIN public.roles r ON ur.role_id = r.id
    ORDER BY ur.user_id,
        CASE r.name
            WHEN 'admin' THEN 1
            WHEN 'coordinator' THEN 2
            WHEN 'technician' THEN 3
            WHEN 'repairmaster' THEN 4
            WHEN 'customer' THEN 5
            ELSE 6
        END ASC
)
SELECT 
    u.id,
    u.email,
    u.name AS full_name,
    u.phone,
    u.address,
    u.created_at,
    COALESCE(uhr.role_name, 'customer') AS role
FROM public.users u
LEFT JOIN user_highest_role uhr ON u.id = uhr.user_id;

-- 16. Grant Permissions on Profiles View to Public API Roles
GRANT SELECT ON public.profiles TO authenticated, anon;