-- =============================================
-- Migration: 004_create_documents_table
-- Description: Resmi belgeler ve dosyalar tablosu
-- Created: 2026-01-07
-- =============================================

-- Belge türleri
CREATE TYPE document_type AS ENUM (
    'sozlesme',           -- İki taraflı imzalı sözleşme
    'yetki_belgesi',      -- Yetki belgesi
    'sertifika',          -- Sertifikalar
    'egitim_belgesi',     -- Eğitim katılım belgesi
    'saglik_raporu',      -- Sağlık raporları
    'risk_raporu',        -- Risk analizi raporu
    'acil_durum_plani',   -- Acil durum planı
    'denetim_raporu',     -- Denetim raporları
    'diger'               -- Diğer belgeler
);

-- Belge durumu
CREATE TYPE document_status AS ENUM ('draft', 'pending_signature', 'signed', 'archived');

-- Documents table
CREATE TABLE IF NOT EXISTS documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
    service_id UUID REFERENCES company_services(id) ON DELETE SET NULL,
    document_type document_type NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    file_url TEXT, -- Supabase Storage URL
    file_name TEXT,
    file_size INTEGER, -- bytes
    mime_type TEXT,
    status document_status DEFAULT 'draft',
    valid_from DATE,
    valid_until DATE,
    signed_at TIMESTAMPTZ,
    uploaded_by UUID REFERENCES profiles(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_documents_company_id ON documents(company_id);
CREATE INDEX idx_documents_service_id ON documents(service_id);
CREATE INDEX idx_documents_document_type ON documents(document_type);
CREATE INDEX idx_documents_status ON documents(status);
CREATE INDEX idx_documents_validity ON documents(valid_from, valid_until);

-- Apply updated_at trigger
CREATE TRIGGER update_documents_updated_at
    BEFORE UPDATE ON documents
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS Policies
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Admins full access
CREATE POLICY "Admins full access to documents" ON documents
    FOR ALL
    USING (
        EXISTS (
            SELECT 1 FROM profiles 
            WHERE id = auth.uid() AND role = 'admin'
        )
    );

-- Companies can view their own documents
CREATE POLICY "Companies can view own documents" ON documents
    FOR SELECT
    USING (
        company_id IN (
            SELECT id FROM companies WHERE profile_id = auth.uid()
        )
    );