-- Table des PV validés en attente de vote
CREATE TABLE pv_valides (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_pv_preparation INT NOT NULL,
    id_copro INT NOT NULL,
    date_ag DATETIME NOT NULL,
    lieu VARCHAR(500) NOT NULL,
    heure VARCHAR(10) NOT NULL,
    statut ENUM('en_attente', 'en_cours', 'clos') DEFAULT 'en_attente',
    vote_ouvert TINYINT DEFAULT 0,
    date_ouverture_vote DATETIME NULL,
    date_fermeture_vote DATETIME NULL,
    quorum_atteint TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pv_preparation) REFERENCES prepare_pv(id),
    FOREIGN KEY (id_copro) REFERENCES syndics(id_copro),
    INDEX idx_copro_statut (id_copro, statut)
);

-- Table des résolutions à voter
CREATE TABLE pv_resolutions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_pv_valide INT NOT NULL,
    id_copro INT NOT NULL,
    numero_resolution INT NOT NULL,
    titre VARCHAR(500) NOT NULL,
    texte TEXT NOT NULL,
    article_loi VARCHAR(100) NULL,
    majorite_requise ENUM('simple', 'absolue', 'double') NOT NULL,
    ordre_affichage INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pv_valide) REFERENCES pv_valides(id) ON DELETE CASCADE,
    FOREIGN KEY (id_copro) REFERENCES syndics(id_copro),
    UNIQUE KEY unique_resolution (id_pv_valide, numero_resolution)
);

-- Table des votes des copropriétaires
CREATE TABLE pv_votes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_resolution INT NOT NULL,
    id_copro INT NOT NULL,
    id_coproprietaire INT NOT NULL,
    vote ENUM('pour', 'contre', 'abstention') NOT NULL,
    tantiemes DECIMAL(10,3) NOT NULL,
    vote_par_procuration TINYINT DEFAULT 0,
    id_mandataire INT NULL COMMENT 'Si vote par procuration',
    date_vote TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    FOREIGN KEY (id_resolution) REFERENCES pv_resolutions(id) ON DELETE CASCADE,
    FOREIGN KEY (id_copro) REFERENCES syndics(id_copro),
    FOREIGN KEY (id_coproprietaire) REFERENCES users(id),
    FOREIGN KEY (id_mandataire) REFERENCES users(id),
    UNIQUE KEY unique_vote (id_resolution, id_coproprietaire),
    INDEX idx_resolution_vote (id_resolution, vote)
);

-- Table des procurations
CREATE TABLE pv_procurations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_pv_valide INT NOT NULL,
    id_copro INT NOT NULL,
    id_mandant INT NOT NULL COMMENT 'Celui qui donne procuration',
    id_mandataire INT NOT NULL COMMENT 'Celui qui reçoit procuration',
    date_procuration DATE NOT NULL,
    document_path VARCHAR(500) NULL COMMENT 'Scan de la procuration',
    valide TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pv_valide) REFERENCES pv_valides(id) ON DELETE CASCADE,
    FOREIGN KEY (id_copro) REFERENCES syndics(id_copro),
    FOREIGN KEY (id_mandant) REFERENCES users(id),
    FOREIGN KEY (id_mandataire) REFERENCES users(id),
    UNIQUE KEY unique_procuration (id_pv_valide, id_mandant),
    INDEX idx_mandataire (id_mandataire)
);

-- Table de logs pour traçabilité juridique
CREATE TABLE pv_audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_pv_valide INT NOT NULL,
    id_copro INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    details JSON NULL,
    id_user INT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pv_valide) REFERENCES pv_valides(id) ON DELETE CASCADE,
    FOREIGN KEY (id_copro) REFERENCES syndics(id_copro),
    INDEX idx_pv_action (id_pv_valide, action),
    INDEX idx_timestamp (timestamp)
);

-- Ajouter colonnes pour validation dans prepare_pv
ALTER TABLE prepare_pv ADD COLUMN date_validation TIMESTAMP NULL AFTER validated;
ALTER TABLE prepare_pv ADD COLUMN id_validateur INT NULL AFTER date_validation;
