CREATE DATABASE IF NOT EXISTS alfa_prospect CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE alfa_prospect;

CREATE TABLE IF NOT EXISTS residences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_registre VARCHAR(50) NULL,
    nom VARCHAR(255) NOT NULL,
    categorie VARCHAR(20) NULL,
    adresse TEXT NULL,
    ville VARCHAR(120) NULL,
    region VARCHAR(150) NULL,
    code_postal VARCHAR(20) NULL,
    telephone VARCHAR(60) NULL,
    courriel VARCHAR(255) NULL,
    site_web VARCHAR(255) NULL,
    nb_unites INT NULL,
    score_priorite INT DEFAULT 0,
    statut ENUM('nouveau','a_appeler','message_laisse','rappel','interesse','demo','client','refus','ne_plus_appeler','mauvais_numero') DEFAULT 'nouveau',
    notes_generales TEXT NULL,
    derniere_action DATETIME NULL,
    prochaine_relance DATE NULL,
    source_import VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_registre (numero_registre),
    INDEX idx_statut (statut),
    INDEX idx_ville (ville),
    INDEX idx_score (score_priorite)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS appels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    residence_id INT NOT NULL,
    date_appel DATETIME DEFAULT CURRENT_TIMESTAMP,
    resultat VARCHAR(100) NOT NULL,
    notes TEXT NULL,
    prochaine_relance DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (residence_id) REFERENCES residences(id) ON DELETE CASCADE,
    INDEX idx_residence (residence_id),
    INDEX idx_date_appel (date_appel)
) ENGINE=InnoDB;