Sentra Automotive — Database Design¶

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

-- 1. Table Customers & Vehicles
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE vehicles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    license_plate VARCHAR(20) UNIQUE NOT NULL, -- e.g., 'B 1234 ABC'
    vin_number VARCHAR(30) UNIQUE NOT NULL,    -- Nomor Rangka
    engine_number VARCHAR(30) UNIQUE NOT NULL, -- Nomor Mesin
    brand_model VARCHAR(100) NOT NULL,         -- e.g., 'Honda Civic 1.5 Turbo'
    production_year INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_vehicles_plate ON vehicles(license_plate);
CREATE INDEX idx_vehicles_vin ON vehicles(vin_number);

-- 2. Table Work Orders (WO)
CREATE TABLE work_orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    vehicle_id UUID NOT NULL REFERENCES vehicles(id) ON DELETE RESTRICT,
    branch_id UUID NOT NULL,
    service_advisor_id UUID NOT NULL,
    wo_number VARCHAR(50) UNIQUE NOT NULL, -- e.g., 'WO/2026/05/0091'
    current_odometer INTEGER NOT NULL,
    customer_complaints TEXT NOT NULL,
    inspection_checklist JSONB DEFAULT '{}'::jsonb NOT NULL,
    status VARCHAR(20) CHECK (status IN (
        'INTAKE', 'DIAGNOSING', 'WORKING', 'WAITING_PARTS',
        'QC_TEST', 'COMPLETED', 'CANCELLED', 'CLAIM_REWORK'
    )) DEFAULT 'INTAKE' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_wo_number ON work_orders(wo_number);
CREATE INDEX idx_wo_status ON work_orders(status);

-- 3. Table Spareparts Master, Batches, & Substitutions
CREATE TABLE spareparts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sku_number VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    brand VARCHAR(50) NOT NULL,
    category VARCHAR(50) CHECK (category IN ('FAST_MOVING', 'SLOW_MOVING', 'ACCESSORIES')) NOT NULL,
    unit VARCHAR(20) NOT NULL, -- e.g., 'Pcs', 'Liter', 'Set'
    barcode_data VARCHAR(100) UNIQUE NOT NULL,
    min_stock_alert INTEGER DEFAULT 10 NOT NULL
);

CREATE TABLE sparepart_batches (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sparepart_id UUID NOT NULL REFERENCES spareparts(id) ON DELETE RESTRICT,
    batch_number VARCHAR(50) NOT NULL,
    current_stock INTEGER NOT NULL CHECK (current_stock >= 0),
    purchase_price NUMERIC(12,2) NOT NULL,
    selling_price NUMERIC(12,2) NOT NULL,
    UNIQUE (sparepart_id, batch_number)
);

-- Substitute mapping: EXACT (drop-in replacement) or MODIFIED (compatible with adjustment)
CREATE TABLE sparepart_substitutions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    parent_sparepart_id UUID NOT NULL REFERENCES spareparts(id) ON DELETE CASCADE,
    substitute_sparepart_id UUID NOT NULL REFERENCES spareparts(id) ON DELETE CASCADE,
    compatibility_level VARCHAR(20) CHECK (compatibility_level IN ('EXACT', 'MODIFIED')) NOT NULL,
    UNIQUE (parent_sparepart_id, substitute_sparepart_id)
);

-- 4. Table Work Order Job Items (Labor & Parts mapping in SPK)
CREATE TABLE job_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    work_order_id UUID NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    mechanic_id UUID REFERENCES users(id) ON DELETE RESTRICT,
    item_type VARCHAR(20) CHECK (item_type IN ('SERVICE_LABOR', 'SPAREPART')) NOT NULL,
    sparepart_id UUID REFERENCES spareparts(id) ON DELETE RESTRICT, -- Null if SERVICE_LABOR
    name VARCHAR(150) NOT NULL,
    quantity INTEGER DEFAULT 1 NOT NULL CHECK (quantity > 0),
    estimated_price NUMERIC(12,2) NOT NULL,
    final_price NUMERIC(12,2) NOT NULL,
    status VARCHAR(30) CHECK (status IN (
        'PENDING', 'APPROVED_BY_CUSTOMER', 'REJECTED_BY_CUSTOMER', 'COMPLETED'
    )) DEFAULT 'PENDING' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. Table Invoices
CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    work_order_id UUID UNIQUE NOT NULL REFERENCES work_orders(id) ON DELETE RESTRICT,
    invoice_number VARCHAR(50) UNIQUE NOT NULL, -- e.g., 'INV/2026/05/0091'
    parts_subtotal NUMERIC(12,2) DEFAULT 0.00 NOT NULL,
    services_subtotal NUMERIC(12,2) DEFAULT 0.00 NOT NULL,
    tax_amount NUMERIC(12,2) DEFAULT 0.00 NOT NULL,  -- PPN 11%
    grand_total NUMERIC(12,2) DEFAULT 0.00 NOT NULL,
    down_payment NUMERIC(12,2) DEFAULT 0.00 NOT NULL,
    payment_status VARCHAR(20) CHECK (payment_status IN (
        'UNPAID', 'PAID', 'REFUNDED', 'ABANDONED_ALERT'
    )) DEFAULT 'UNPAID' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 6. Table Mechanic Commissions & Payroll
CREATE TABLE mechanic_commissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    mechanic_id UUID NOT NULL,
    job_item_id UUID UNIQUE NOT NULL REFERENCES job_items(id) ON DELETE RESTRICT,
    commission_type VARCHAR(20) CHECK (commission_type IN (
        'FLAT_RATE', 'PERCENTAGE', 'UPSELL_BONUS'
    )) NOT NULL,
    commission_amount NUMERIC(12,2) NOT NULL,
    calculated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 7. Auto-reorder purchase requisitions
CREATE TABLE purchase_requisitions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sparepart_id UUID NOT NULL REFERENCES spareparts(id) ON DELETE RESTRICT,
    branch_id UUID NOT NULL,
    quantity_requested INTEGER NOT NULL CHECK (quantity_requested > 0),
    status VARCHAR(30) CHECK (status IN (
        'PENDING_SUPPLIER_APPROVAL', 'APPROVED', 'FULFILLED', 'CANCELLED'
    )) DEFAULT 'PENDING_SUPPLIER_APPROVAL' NOT NULL,
    reason TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);