-- SIMATA PostgreSQL Schema (Supabase)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 1. Table: profiles
CREATE TABLE IF NOT EXISTS public.profiles (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
nama TEXT NOT NULL,
email TEXT,
role VARCHAR(50) NOT NULL DEFAULT 'operator' CHECK (role IN ('admin', 'operator', 'pimpinan')),
unit_kerja TEXT,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 2. Table: vehicles (Data Aset)
CREATE TABLE IF NOT EXISTS public.vehicles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
nomor INTEGER,
kode_barang VARCHAR(100),
jenis VARCHAR(50) NOT NULL DEFAULT 'Mobil',
no_register VARCHAR(100),
merk_type VARCHAR(255) NOT NULL,
ukuran_cc VARCHAR(50),
bahan VARCHAR(100),
tahun INTEGER,
nomor_rangka VARCHAR(100),
nomor_mesin VARCHAR(100),
nomor_polisi VARCHAR(50) NOT NULL,
bpkb VARCHAR(100),
asal_usul VARCHAR(100),
harga NUMERIC(18, 2) DEFAULT 0,
nama_pengguna VARCHAR(255),
unit_kerja_pengguna VARCHAR(255),
periode_pajak VARCHAR(50) DEFAULT '2026',
jatuh_tempo DATE,
status_pajak VARCHAR(50) DEFAULT 'Belum Bayar' CHECK (status_pajak IN ('Belum Bayar', 'Segera Bayar', 'Sudah Bayar')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 3. RLS Permissive Policy
ALTER TABLE public.vehicles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Vehicles: Allow full access" ON public.vehicles;
CREATE POLICY "Vehicles: Allow full access" ON public.vehicles FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);