-- Migration pour le système de NAA (Note d'Apporteur d'Affaires)

-- 1. Ajouter les colonnes à organization_details pour gérer les apporteurs d'affaires
ALTER TABLE organization_details 
ADD COLUMN IF NOT EXISTS is_referred BOOLEAN DEFAULT false,
ADD COLUMN IF NOT EXISTS referrer_code VARCHAR(50),
ADD COLUMN IF NOT EXISTS commission_rate DECIMAL(6,4) DEFAULT 0.0;

-- Commentaires pour les nouvelles colonnes
COMMENT ON COLUMN organization_details.is_referred IS 'Indique si le client a été apporté par un apporteur d''affaires';
COMMENT ON COLUMN organization_details.referrer_code IS 'Code de l''apporteur d''affaires (ex: LDP pour La Douce Prod)';
COMMENT ON COLUMN organization_details.commission_rate IS 'Taux de commission pour l''apporteur (ex: 0.20 pour 20%)';

-- 2. Créer la table des apporteurs d'affaires
CREATE TABLE IF NOT EXISTS referrers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255),
    address VARCHAR(255),
    postal_code VARCHAR(10),
    city VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE referrers IS 'Table des apporteurs d''affaires';

-- Insérer l'apporteur La Douce Prod
INSERT INTO referrers (code, name, contact_name, address, postal_code, city)
VALUES ('LDP', 'La Douce Prod', 'Raphaël LEFEBVRE', '16 rue Nationale', '59000', 'LILLE')
ON CONFLICT (code) DO NOTHING;

-- 3. Créer la table des NAA
CREATE TABLE IF NOT EXISTS naa_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    naa_number VARCHAR(20) UNIQUE NOT NULL,
    referrer_id UUID REFERENCES referrers(id) ON DELETE CASCADE,
    referrer_code VARCHAR(50) NOT NULL,
    periode VARCHAR(50) NOT NULL, -- ex: "Février 2025"
    callsheet_date DATE NOT NULL,
    limit_date DATE,
    total_commission DECIMAL(10,2) DEFAULT 0.0,
    solde_compte_apporteur DECIMAL(10,2) DEFAULT 0.0,
    total_facture DECIMAL(10,2) DEFAULT 0.0,
    deposit DECIMAL(10,2) DEFAULT 0.0,
    nbre_clients INTEGER DEFAULT 0,
    nbre_prestations INTEGER DEFAULT 0,
    transfer_reference VARCHAR(100),
    pdf_url TEXT,
    s3_key TEXT,
    status VARCHAR(20) DEFAULT 'draft', -- draft, sent, paid
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID
);

COMMENT ON TABLE naa_documents IS 'Table des Notes d''Apporteur d''Affaires (NAA)';
COMMENT ON COLUMN naa_documents.naa_number IS 'Numéro unique de la NAA (ex: NAA-000001)';
COMMENT ON COLUMN naa_documents.status IS 'Statut de la NAA: draft, sent, paid';

-- Index pour les recherches
CREATE INDEX IF NOT EXISTS idx_naa_documents_referrer_id ON naa_documents(referrer_id);
CREATE INDEX IF NOT EXISTS idx_naa_documents_periode ON naa_documents(periode);
CREATE INDEX IF NOT EXISTS idx_naa_documents_status ON naa_documents(status);
CREATE INDEX IF NOT EXISTS idx_organization_details_referrer ON organization_details(referrer_code) WHERE is_referred = true;

-- 4. Créer la table des lignes de commissions (line items)
CREATE TABLE IF NOT EXISTS naa_line_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    naa_id UUID REFERENCES naa_documents(id) ON DELETE CASCADE,
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    client_name VARCHAR(255) NOT NULL,
    client_code VARCHAR(50),
    commission_rate DECIMAL(6,4) NOT NULL,
    ca_ht DECIMAL(10,2) NOT NULL,
    commission DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE naa_line_items IS 'Lignes de commission par client pour chaque NAA';

CREATE INDEX IF NOT EXISTS idx_naa_line_items_naa_id ON naa_line_items(naa_id);
CREATE INDEX IF NOT EXISTS idx_naa_line_items_organization_id ON naa_line_items(organization_id);

-- 5. Créer la table des prestations NAA
CREATE TABLE IF NOT EXISTS naa_prestations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    naa_id UUID REFERENCES naa_documents(id) ON DELETE CASCADE,
    client_name VARCHAR(255) NOT NULL,
    client_code VARCHAR(50),
    type_prestation VARCHAR(100) NOT NULL,
    quantite INTEGER NOT NULL,
    tarif DECIMAL(10,2) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE naa_prestations IS 'Détail des prestations pour chaque NAA';

CREATE INDEX IF NOT EXISTS idx_naa_prestations_naa_id ON naa_prestations(naa_id);

-- 6. Fonction pour générer automatiquement le numéro de NAA
CREATE OR REPLACE FUNCTION generate_naa_number()
RETURNS TEXT AS $$
DECLARE
    next_number INTEGER;
    formatted_number TEXT;
BEGIN
    -- Récupérer le dernier numéro
    SELECT COALESCE(MAX(CAST(SUBSTRING(naa_number FROM 5) AS INTEGER)), 0) + 1
    INTO next_number
    FROM naa_documents
    WHERE naa_number LIKE 'NAA-%';
    
    -- Formater avec des zéros
    formatted_number := 'NAA-' || LPAD(next_number::TEXT, 6, '0');
    
    RETURN formatted_number;
END;
$$ LANGUAGE plpgsql;

-- 7. Trigger pour mettre à jour updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_referrers_updated_at
    BEFORE UPDATE ON referrers
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_naa_documents_updated_at
    BEFORE UPDATE ON naa_documents
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 8. Activer Row Level Security (RLS)
ALTER TABLE referrers ENABLE ROW LEVEL SECURITY;
ALTER TABLE naa_documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE naa_line_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE naa_prestations ENABLE ROW LEVEL SECURITY;

-- Policies pour le staff uniquement
CREATE POLICY "Staff can view all referrers" ON referrers
    FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can manage all referrers" ON referrers
    FOR ALL TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can view all NAA documents" ON naa_documents
    FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can manage all NAA documents" ON naa_documents
    FOR ALL TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can view all NAA line items" ON naa_line_items
    FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can manage all NAA line items" ON naa_line_items
    FOR ALL TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can view all NAA prestations" ON naa_prestations
    FOR SELECT TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );

CREATE POLICY "Staff can manage all NAA prestations" ON naa_prestations
    FOR ALL TO authenticated
    USING (
        EXISTS (
            SELECT 1 FROM staff_users
            WHERE staff_users.user_id = auth.uid()
            AND staff_users.is_staff = true
        )
    );