mirror of
http://forgejo-oa09toasww4dgii9cj3gpzda.187.127.164.61.sslip.io/iamcoolvivek007/bharath.git
synced 2026-06-11 00:06:51 +00:00
Vivek
ed320e82c1
- Multi-language support (English, Hindi, Tamil, Telugu) with icon-based UI - Voice input (Web Speech API) for low-literacy users - Driver tools: Ledger, Trip Planner, Return Load, Safety, Maintenance, FASTag - Marketplace: WhatsApp share, Rate Intelligence, Classifieds, Fleet - Engagement: Gamification (XP/Levels), Challenges, Leaderboard, Referrals, Feed - Business: Invoice (GST+UPI), Reports+CSV, Notifications, Documents, Bank - Games: Rate Guesser, Route Quiz - SEO: Sitemap, public load share pages with OG tags - India utilities: vehicle validation, UPI links, toll/fuel calculator - 29 routes, 54 templates, 4 languages, 3 migration files
106 lines
4 KiB
SQL
106 lines
4 KiB
SQL
-- 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);
|