-- ============================================================================
-- CRÉATION DES TABLES POUR LA GESTION DES TRAVAUX
-- ============================================================================

-- Table principale des travaux
CREATE TABLE IF NOT EXISTS travaux (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    titre TEXT NOT NULL,
    description TEXT,
    date_debut DATE NOT NULL,
    date_fin DATE NOT NULL,
    jours_travaux TEXT[] NOT NULL, -- ['lundi', 'mardi', 'mercredi', 'jeudi', 'vendredi', 'samedi', 'dimanche']
    heure_debut TIME NOT NULL,
    heure_fin TIME NOT NULL,
    actif BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    deleted_at TIMESTAMP WITH TIME ZONE,

    CONSTRAINT valid_dates CHECK (date_fin >= date_debut),
    CONSTRAINT valid_hours CHECK (heure_fin > heure_debut)
);

-- Table de liaison entre travaux et sillons de substitution
CREATE TABLE IF NOT EXISTS travaux_substitutions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    travaux_id UUID NOT NULL REFERENCES travaux(id) ON DELETE CASCADE,
    horaire_id UUID NOT NULL REFERENCES horaires(id) ON DELETE CASCADE,
    ordre INTEGER DEFAULT 0, -- Ordre d'affichage des substitutions
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),

    UNIQUE(travaux_id, horaire_id)
);

-- Index pour améliorer les performances
CREATE INDEX IF NOT EXISTS idx_travaux_dates ON travaux(date_debut, date_fin);
CREATE INDEX IF NOT EXISTS idx_travaux_actif ON travaux(actif);
CREATE INDEX IF NOT EXISTS idx_travaux_deleted ON travaux(deleted_at);
CREATE INDEX IF NOT EXISTS idx_travaux_substitutions_travaux ON travaux_substitutions(travaux_id);
CREATE INDEX IF NOT EXISTS idx_travaux_substitutions_horaire ON travaux_substitutions(horaire_id);

-- Fonction pour mettre à jour updated_at automatiquement
CREATE OR REPLACE FUNCTION update_travaux_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger pour mettre à jour updated_at
DROP TRIGGER IF EXISTS trigger_update_travaux_updated_at ON travaux;
CREATE TRIGGER trigger_update_travaux_updated_at
    BEFORE UPDATE ON travaux
    FOR EACH ROW
    EXECUTE FUNCTION update_travaux_updated_at();

-- Commentaires sur les tables
COMMENT ON TABLE travaux IS 'Table principale pour la gestion des travaux sur les lignes';
COMMENT ON TABLE travaux_substitutions IS 'Table de liaison entre travaux et horaires de substitution';

COMMENT ON COLUMN travaux.jours_travaux IS 'Jours de la semaine où les travaux ont lieu';
COMMENT ON COLUMN travaux.actif IS 'Indique si les travaux sont actifs';
COMMENT ON COLUMN travaux.deleted_at IS 'Date de suppression (soft delete)';
COMMENT ON COLUMN travaux_substitutions.ordre IS 'Ordre d''affichage des substitutions';

-- ============================================================================
-- DONNÉES DE TEST (optionnel)
-- ============================================================================

-- Exemple de travaux
INSERT INTO travaux (titre, description, date_debut, date_fin, jours_travaux, heure_debut, heure_fin, actif)
VALUES
    ('Travaux ligne Paris-Lyon', 'Rénovation des voies', '2026-03-01', '2026-03-31', ARRAY['lundi', 'mardi', 'mercredi', 'jeudi', 'vendredi'], '09:00', '17:00', true),
    ('Maintenance gare de Lyon', 'Travaux de maintenance planifiés', '2026-04-15', '2026-04-20', ARRAY['samedi', 'dimanche'], '06:00', '22:00', true)
ON CONFLICT DO NOTHING;

-- Afficher le résultat
SELECT
    'Tables travaux créées avec succès' as statut,
    (SELECT COUNT(*) FROM travaux) as nombre_travaux,
    (SELECT COUNT(*) FROM travaux_substitutions) as nombre_substitutions;