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
91 lines
3.2 KiB
SQL
91 lines
3.2 KiB
SQL
-- BharathTrucks Phase 2 Migration: Gamification, Referral, Feed, Challenges, Invoice
|
|
-- Run AFTER supabase-phase1-migration.sql
|
|
|
|
CREATE TABLE IF NOT EXISTS user_gamification (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES app_users(id) ON DELETE CASCADE UNIQUE,
|
|
xp INTEGER DEFAULT 0,
|
|
login_streak INTEGER DEFAULT 0,
|
|
last_login_date DATE,
|
|
steps_completed JSONB DEFAULT '[]',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_achievements (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
|
|
achievement_id TEXT NOT NULL,
|
|
earned_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(user_id, achievement_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS xp_log (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
|
|
action TEXT NOT NULL,
|
|
xp_earned INTEGER DEFAULT 0,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX idx_xp_log_user ON xp_log(user_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS challenge_progress (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
|
|
challenge_id TEXT NOT NULL,
|
|
completed_date DATE DEFAULT CURRENT_DATE,
|
|
UNIQUE(user_id, challenge_id, completed_date)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS referrals (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
referrer_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
|
|
referred_user_id UUID,
|
|
referral_code TEXT,
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX idx_referrals_referrer ON referrals(referrer_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS feed_events (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
event_type TEXT NOT NULL,
|
|
data JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX idx_feed_created ON feed_events(created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS invoices (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES app_users(id) ON DELETE CASCADE,
|
|
invoice_number TEXT UNIQUE,
|
|
client_name TEXT,
|
|
origin TEXT,
|
|
destination TEXT,
|
|
amount NUMERIC(10,2) DEFAULT 0,
|
|
gst_rate NUMERIC(4,2) DEFAULT 5,
|
|
gst_amount NUMERIC(10,2) DEFAULT 0,
|
|
total_amount NUMERIC(10,2) DEFAULT 0,
|
|
upi_id TEXT,
|
|
upi_link TEXT,
|
|
notes TEXT,
|
|
status TEXT DEFAULT 'unpaid',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX idx_invoices_user ON invoices(user_id, created_at DESC);
|
|
|
|
-- RLS
|
|
ALTER TABLE user_gamification ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_achievements ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE xp_log ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE challenge_progress ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE referrals ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE feed_events ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "all_access" ON user_gamification FOR ALL USING (true);
|
|
CREATE POLICY "all_access" ON user_achievements FOR ALL USING (true);
|
|
CREATE POLICY "all_access" ON xp_log FOR ALL USING (true);
|
|
CREATE POLICY "all_access" ON challenge_progress FOR ALL USING (true);
|
|
CREATE POLICY "all_access" ON referrals FOR ALL USING (true);
|
|
CREATE POLICY "all_access" ON feed_events FOR ALL USING (true);
|
|
CREATE POLICY "all_access" ON invoices FOR ALL USING (true);
|