-- BharathTrucks Phase 1 Migration: Driver tools, Safety, Maintenance, FASTag, Notifications
-- Run this AFTER supabase-FULL-migration.sql

-- Driver personal ledger (replaces paper diary)
CREATE TABLE IF NOT EXISTS driver_ledger (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
  origin TEXT,
  destination TEXT,
  trip_date DATE DEFAULT CURRENT_DATE,
  freight_received NUMERIC(10,2) DEFAULT 0,
  fuel_cost NUMERIC(10,2) DEFAULT 0,
  toll_cost NUMERIC(10,2) DEFAULT 0,
  other_expense NUMERIC(10,2) DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_driver_ledger_user ON driver_ledger(user_id);

-- Return load availability
CREATE TABLE IF NOT EXISTS available_for_return (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE UNIQUE,
  current_city TEXT NOT NULL,
  home_city TEXT,
  vehicle_type TEXT,
  status TEXT DEFAULT 'looking',
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_return_city ON available_for_return(current_city, status);

-- Safety contacts
CREATE TABLE IF NOT EXISTS safety_contacts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
  contact_name TEXT NOT NULL,
  contact_phone TEXT NOT NULL,
  relationship TEXT DEFAULT 'family',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_safety_contacts_user ON safety_contacts(user_id);

-- Safety check-ins log
CREATE TABLE IF NOT EXISTS safety_checkins (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
  location TEXT,
  message TEXT,
  is_sos BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Vehicle maintenance reminders
CREATE TABLE IF NOT EXISTS vehicle_reminders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
  vehicle_number TEXT,
  doc_type TEXT NOT NULL, -- insurance, fitness, permit, puc, service
  expiry_date DATE NOT NULL,
  notes TEXT,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_reminders_user ON vehicle_reminders(user_id);
CREATE INDEX idx_reminders_expiry ON vehicle_reminders(expiry_date, status);

-- FASTag accounts
CREATE TABLE IF NOT EXISTS fastag_accounts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE UNIQUE,
  fastag_number TEXT,
  vehicle_number TEXT,
  issuer_bank TEXT,
  balance NUMERIC(10,2) DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Toll history
CREATE TABLE IF NOT EXISTS toll_history (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
  type TEXT DEFAULT 'toll', -- toll, recharge
  plaza_name TEXT,
  amount NUMERIC(10,2) DEFAULT 0,
  status TEXT DEFAULT 'completed',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_toll_user ON toll_history(user_id, created_at DESC);

-- Enable RLS
ALTER TABLE driver_ledger ENABLE ROW LEVEL SECURITY;
ALTER TABLE available_for_return ENABLE ROW LEVEL SECURITY;
ALTER TABLE safety_contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE safety_checkins ENABLE ROW LEVEL SECURITY;
ALTER TABLE vehicle_reminders ENABLE ROW LEVEL SECURITY;
ALTER TABLE fastag_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE toll_history ENABLE ROW LEVEL SECURITY;

-- RLS policies (allow all for service role, restrict for anon)
CREATE POLICY "Users manage own ledger" ON driver_ledger FOR ALL USING (true);
CREATE POLICY "Users manage own return" ON available_for_return FOR ALL USING (true);
CREATE POLICY "Users manage own contacts" ON safety_contacts FOR ALL USING (true);
CREATE POLICY "Users manage own checkins" ON safety_checkins FOR ALL USING (true);
CREATE POLICY "Users manage own reminders" ON vehicle_reminders FOR ALL USING (true);
CREATE POLICY "Users manage own fastag" ON fastag_accounts FOR ALL USING (true);
CREATE POLICY "Users manage own tolls" ON toll_history FOR ALL USING (true);