supabase-migration-complete.sql

-- Migration complète : Création des tables et ajout des colonnes tax_rate
-- Exécutez ce script dans l'éditeur SQL de Supabase

-- 1. Créer la table user_settings si elle n'existe pas
CREATE TABLE IF NOT EXISTS user_settings (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
  company_info JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- Index pour user_settings
CREATE INDEX IF NOT EXISTS idx_user_settings_user_id ON user_settings(user_id);

-- RLS pour user_settings
ALTER TABLE user_settings ENABLE ROW LEVEL SECURITY;

-- Politiques RLS pour user_settings (si elles n'existent pas déjà)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'user_settings' 
    AND policyname = 'Users can view their own settings'
  ) THEN
    CREATE POLICY "Users can view their own settings"
      ON user_settings FOR SELECT
      USING (auth.uid() = user_id);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'user_settings' 
    AND policyname = 'Users can insert their own settings'
  ) THEN
    CREATE POLICY "Users can insert their own settings"
      ON user_settings FOR INSERT
      WITH CHECK (auth.uid() = user_id);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'user_settings' 
    AND policyname = 'Users can update their own settings'
  ) THEN
    CREATE POLICY "Users can update their own settings"
      ON user_settings FOR UPDATE
      USING (auth.uid() = user_id);
  END IF;
END $$;

-- Fonction pour mettre à jour updated_at automatiquement (si elle n'existe pas)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = TIMEZONE('utc'::text, NOW());
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger pour updated_at sur user_settings (si il n'existe pas)
DROP TRIGGER IF EXISTS update_user_settings_updated_at ON user_settings;
CREATE TRIGGER update_user_settings_updated_at
  BEFORE UPDATE ON user_settings
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- 2. Ajouter la colonne tax_rate à la table devis si elle n'existe pas déjà
ALTER TABLE devis 
ADD COLUMN IF NOT EXISTS tax_rate DECIMAL(5, 2) DEFAULT 20.00;

-- Mettre à jour les devis existants avec un taux de TVA par défaut de 20%
UPDATE devis 
SET tax_rate = 20.00 
WHERE tax_rate IS NULL;

-- 3. Ajouter la colonne default_tax_rate à la table user_settings si elle n'existe pas déjà
ALTER TABLE user_settings 
ADD COLUMN IF NOT EXISTS default_tax_rate DECIMAL(5, 2) DEFAULT 20.00;