freightdesk/supabase/migrations/001_initial_schema.sql

-- ============================================================
-- FreightDesk — Supabase Migration
-- Initial schema for freight forwarding commission agent
-- ============================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================
-- SHIPPERS
-- ============================================================
CREATE TABLE shippers (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    phone TEXT,
    email TEXT,
    city TEXT DEFAULT 'Thiruvananthapuram',
    state TEXT DEFAULT 'Kerala',
    gst TEXT,
    total_freight NUMERIC(12,2) DEFAULT 0,
    total_commission NUMERIC(12,2) DEFAULT 0,
    pending_amount NUMERIC(12,2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- VEHICLES
-- ============================================================
CREATE TABLE vehicles (
    id TEXT PRIMARY KEY,
    number TEXT NOT NULL UNIQUE,
    type TEXT DEFAULT 'open',
    capacity_ton NUMERIC(6,2),
    city TEXT DEFAULT 'Thiruvananthapuram',
    state TEXT DEFAULT 'Kerala',
    owner_name TEXT,
    owner_phone TEXT,
    is_active BOOLEAN DEFAULT true,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- LOADS (core table)
-- ============================================================
CREATE TABLE loads (
    id TEXT PRIMARY KEY,
    date DATE,
    vehicle_id TEXT REFERENCES vehicles(id) ON DELETE SET NULL,
    from_city TEXT,
    via TEXT,
    to_city TEXT,
    shipper_id TEXT REFERENCES shippers(id) ON DELETE SET NULL,
    load_type TEXT,
    item TEXT,
    freight_charged NUMERIC(12,2),
    advance_received NUMERIC(12,2),
    paid_to_driver NUMERIC(12,2),
    commission NUMERIC(12,2),
    driver_freight NUMERIC(12,2),
    pending_from_shipper NUMERIC(12,2),
    pending_to_driver NUMERIC(12,2),
    status TEXT DEFAULT 'partial',
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- PAYMENTS (individual payment transactions)
-- ============================================================
CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    load_id TEXT REFERENCES loads(id) ON DELETE CASCADE,
    type TEXT NOT NULL CHECK (type IN ('advance', 'balance', 'commission', 'driver_payment', 'other')),
    direction TEXT NOT NULL CHECK (direction IN ('in', 'out')),
    amount NUMERIC(12,2) NOT NULL,
    method TEXT DEFAULT 'bank_transfer',
    payment_date DATE,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- PORTAL USERS (for shipper/driver portal access)
-- ============================================================
CREATE TABLE portal_users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('shipper', 'driver', 'admin')),
    entity_id TEXT,  -- links to shippers.id or vehicles.id
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- COMMISSION INVOICES
-- ============================================================
CREATE TABLE commission_invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_no TEXT NOT NULL UNIQUE,
    shipper_id TEXT REFERENCES shippers(id) ON DELETE SET NULL,
    period_from DATE,
    period_to DATE,
    total_commission NUMERIC(12,2) NOT NULL DEFAULT 0,
    status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'sent', 'paid', 'overdue')),
    due_date DATE,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- APP SETTINGS
-- ============================================================
CREATE TABLE app_settings (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX idx_loads_date ON loads(date);
CREATE INDEX idx_loads_status ON loads(status);
CREATE INDEX idx_loads_shipper ON loads(shipper_id);
CREATE INDEX idx_loads_vehicle ON loads(vehicle_id);
CREATE INDEX idx_loads_from_to ON loads(from_city, to_city);
CREATE INDEX idx_payments_load ON payments(load_id);
CREATE INDEX idx_payments_date ON payments(payment_date);
CREATE INDEX idx_vehicles_number ON vehicles(number);
CREATE INDEX idx_shippers_name ON shippers(name);

-- ============================================================
-- UPDATED_AT TRIGGER
-- ============================================================
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER shippers_updated_at BEFORE UPDATE ON shippers
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER vehicles_updated_at BEFORE UPDATE ON vehicles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER loads_updated_at BEFORE UPDATE ON loads
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ============================================================
-- DEFAULT ADMIN USER (password: admin123)
-- bcrypt hash for 'admin123' with 10 rounds
-- ============================================================
INSERT INTO portal_users (username, password_hash, role, is_active)
VALUES ('admin', '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'admin', true);

-- ============================================================
-- DEFAULT SETTINGS
-- ============================================================
INSERT INTO app_settings (key, value) VALUES
    ('app_name', 'FreightDesk'),
    ('app_name_hi', 'फ्रेटडेस्क'),
    ('currency', 'INR'),
    ('invoice_prefix', 'FD'),
    ('default_city', 'Thiruvananthapuram'),
    ('owner_name', ''),
    ('owner_phone', ''),
    ('owner_upi', '');