Database Setup

-- Complete Database Setup Script for Audit Application
-- Run this entire script in your Supabase SQL Editor

-- 1. Create the main imports table to track each import session
CREATE TABLE IF NOT EXISTS public.imports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    filename VARCHAR(255) NOT NULL,
    imported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    total_records INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'processing',
    user_id UUID,
    notes TEXT
);

-- Enable Row Level Security for imports
ALTER TABLE public.imports ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows all operations for now (customize later)
DROP POLICY IF EXISTS "Allow all operations on imports" ON public.imports;
CREATE POLICY "Allow all operations on imports" ON public.imports
    FOR ALL USING (true);

-- 2. Create the main audit data table
CREATE TABLE IF NOT EXISTS public.audit_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    import_id UUID NOT NULL REFERENCES public.imports(id) ON DELETE CASCADE,
    
    -- Document/Transaction identifiers
    document_number VARCHAR(100),
    document_date DATE,
    transaction_type VARCHAR(100),
    
    -- Financial amounts (using NUMERIC for precision)
    debit_amount NUMERIC(15,2),
    credit_amount NUMERIC(15,2),
    balance_amount NUMERIC(15,2),
    
    -- Account information
    account_code VARCHAR(50),
    account_name VARCHAR(255),
    counterparty_account VARCHAR(50),
    counterparty_name VARCHAR(255),
    
    -- Transaction details
    description TEXT,
    reference_number VARCHAR(100),
    currency_code VARCHAR(3) DEFAULT 'BGN',
    
    -- Additional financial fields
    vat_amount NUMERIC(15,2),
    net_amount NUMERIC(15,2),
    gross_amount NUMERIC(15,2),
    
    -- Status and classification
    transaction_status VARCHAR(50),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    
    -- Audit trail
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Additional metadata (JSON for flexible data)
    metadata JSONB
);

-- Enable Row Level Security for audit_data
ALTER TABLE public.audit_data ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows all operations for now
DROP POLICY IF EXISTS "Allow all operations on audit_data" ON public.audit_data;
CREATE POLICY "Allow all operations on audit_data" ON public.audit_data
    FOR ALL USING (true);

-- 3. Create a table for tracking processing errors
CREATE TABLE IF NOT EXISTS public.import_errors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    import_id UUID NOT NULL REFERENCES public.imports(id) ON DELETE CASCADE,
    row_number INTEGER,
    error_type VARCHAR(100),
    error_message TEXT,
    raw_data JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security for import_errors
ALTER TABLE public.import_errors ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows all operations for now
DROP POLICY IF EXISTS "Allow all operations on import_errors" ON public.import_errors;
CREATE POLICY "Allow all operations on import_errors" ON public.import_errors
    FOR ALL USING (true);

-- 4. Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_audit_data_import_id ON public.audit_data(import_id);
CREATE INDEX IF NOT EXISTS idx_audit_data_document_date ON public.audit_data(document_date);
CREATE INDEX IF NOT EXISTS idx_audit_data_account_code ON public.audit_data(account_code);
CREATE INDEX IF NOT EXISTS idx_audit_data_amounts ON public.audit_data(debit_amount, credit_amount);
CREATE INDEX IF NOT EXISTS idx_imports_status ON public.imports(status);
CREATE INDEX IF NOT EXISTS idx_imports_imported_at ON public.imports(imported_at);
CREATE INDEX IF NOT EXISTS idx_audit_data_document_number ON public.audit_data(document_number);
CREATE INDEX IF NOT EXISTS idx_audit_data_counterparty ON public.audit_data(counterparty_name);

-- 5. Create a view for easy data retrieval with import information
CREATE OR REPLACE VIEW public.audit_data_with_import AS
SELECT 
    ad.*,
    i.filename,
    i.imported_at as import_date,
    i.status as import_status
FROM public.audit_data ad
JOIN public.imports i ON ad.import_id = i.id;