227 lines
7.9 KiB
PL/PgSQL
227 lines
7.9 KiB
PL/PgSQL
-- 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
|
|
)
|
|
);
|