-- =============================================
-- Migration: 005_create_quotes_table
-- Description: Teklif talepleri ve teklifler tablosu
-- Created: 2026-01-07
-- =============================================

-- Teklif durumu
CREATE TYPE quote_status AS ENUM (
    'pending',      -- Beklemede (yeni talep)
    'reviewing',    -- İnceleniyor
    'quoted',       -- Teklif verildi
    'accepted',     -- Kabul edildi
    'rejected',     -- Reddedildi
    'expired'       -- Süresi doldu
);

-- Quote requests (from website form)
CREATE TABLE IF NOT EXISTS quote_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- Şirket bilgileri
    company_name TEXT NOT NULL,
    tax_number TEXT,
    authorized_person TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT NOT NULL,
    city TEXT,
    district TEXT,
    address TEXT,
    -- İSG bilgileri
    nace_code TEXT,
    tehlike_sinifi tehlike_sinifi,
    employee_count INTEGER NOT NULL,
    sector TEXT,
    -- Hesaplanan süreler (dakika)
    hekim_suresi INTEGER,
    uzman_suresi INTEGER,
    dsp_suresi INTEGER,
    -- Talep edilen hizmetler (JSON array)
    requested_services JSONB,
    -- Ek notlar
    notes TEXT,
    -- Durum
    status quote_status DEFAULT 'pending',
    -- Admin işlemleri
    reviewed_by UUID REFERENCES profiles(id),
    reviewed_at TIMESTAMPTZ,
    -- Tracking
    source TEXT DEFAULT 'website', -- website, phone, email, referral
    ip_address TEXT,
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Quotes (admin tarafından oluşturulan teklifler)
CREATE TABLE IF NOT EXISTS quotes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    quote_request_id UUID REFERENCES quote_requests(id) ON DELETE SET NULL,
    company_id UUID REFERENCES companies(id) ON DELETE SET NULL,
    quote_number TEXT UNIQUE NOT NULL, -- TEK-2026-0001 formatında
    -- Teklif detayları
    items JSONB NOT NULL, -- [{service_id, name, quantity, unit_price, total}]
    subtotal DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,2) DEFAULT 20.00,
    tax_amount DECIMAL(10,2) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    currency TEXT DEFAULT 'TRY',
    -- Geçerlilik
    valid_until DATE NOT NULL,
    -- Notlar
    terms TEXT,
    notes TEXT,
    -- Durum
    status quote_status DEFAULT 'pending',
    -- PDF
    pdf_url TEXT,
    -- Admin
    created_by UUID REFERENCES profiles(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_quote_requests_status ON quote_requests(status);
CREATE INDEX idx_quote_requests_email ON quote_requests(email);
CREATE INDEX idx_quote_requests_created_at ON quote_requests(created_at DESC);

CREATE INDEX idx_quotes_quote_request_id ON quotes(quote_request_id);
CREATE INDEX idx_quotes_company_id ON quotes(company_id);
CREATE INDEX idx_quotes_status ON quotes(status);
CREATE INDEX idx_quotes_quote_number ON quotes(quote_number);

-- Apply updated_at triggers
CREATE TRIGGER update_quote_requests_updated_at
    BEFORE UPDATE ON quote_requests
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_quotes_updated_at
    BEFORE UPDATE ON quotes
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Quote number sequence
CREATE SEQUENCE quote_number_seq START 1;

-- Function to generate quote number
CREATE OR REPLACE FUNCTION generate_quote_number()
RETURNS TRIGGER AS $$
BEGIN
    NEW.quote_number := 'TEK-' || EXTRACT(YEAR FROM NOW()) || '-' || LPAD(nextval('quote_number_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_quote_number
    BEFORE INSERT ON quotes
    FOR EACH ROW
    WHEN (NEW.quote_number IS NULL)
    EXECUTE FUNCTION generate_quote_number();

-- RLS Policies
ALTER TABLE quote_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE quotes ENABLE ROW LEVEL SECURITY;

-- Anyone can insert quote requests (public form)
CREATE POLICY "Anyone can create quote requests" ON quote_requests
    FOR INSERT
    WITH CHECK (true);

-- Only admins can view/manage quote requests
CREATE POLICY "Admins full access to quote_requests" ON quote_requests
    FOR ALL
    USING (
        EXISTS (
            SELECT 1 FROM profiles 
            WHERE id = auth.uid() AND role = 'admin'
        )
    );

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

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