CREATE TABLE IF NOT EXISTS users (
    id CHAR(36) NOT NULL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tokens (
    id CHAR(36) NOT NULL PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS biens (
    id CHAR(36) NOT NULL PRIMARY KEY,
    source VARCHAR(50),
    type VARCHAR(50) NOT NULL,
    adresse TEXT NOT NULL,
    code_postal VARCHAR(20),
    ville VARCHAR(255) NOT NULL,
    surface DECIMAL(10,2),
    prix_affiche DECIMAL(12,2) NOT NULL,
    prix_negocie DECIMAL(12,2),
    frais_notaire_estime DECIMAL(12,2),
    frais_agence_estime DECIMAL(12,2),
    cout_travaux_estime DECIMAL(12,2),
    charges_mensuelles DECIMAL(12,2),
    taxe_fonciere_annuelle DECIMAL(12,2),
    etat VARCHAR(50),
    statut_commercial VARCHAR(50),
    url_annonce TEXT,
    photos JSON,
    lat DECIMAL(10,7),
    lon DECIMAL(10,7),
    contact_nom VARCHAR(255),
    contact_email VARCHAR(255),
    contact_tel VARCHAR(50),
    contact_notes TEXT,
    rentabilite_cible DECIMAL(5,2),
    date_mise_en_ligne DATE,
    date_mise_a_jour TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS analyses (
    id CHAR(36) NOT NULL PRIMARY KEY,
    bien_id CHAR(36) NOT NULL,
    loyer_estime DECIMAL(12,2),
    rentabilite_brute DECIMAL(5,2),
    rentabilite_nette DECIMAL(5,2),
    cashflow_net_mensuel DECIMAL(12,2),
    tri_10ans DECIMAL(5,2),
    cout_acquisition_total DECIMAL(12,2),
    hypotheses JSON,
    scenario VARCHAR(50),
    notes_ia TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_analyses_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS acquisitions (
    id CHAR(36) NOT NULL PRIMARY KEY,
    bien_id CHAR(36) NOT NULL,
    statut VARCHAR(50),
    prix_negocie DECIMAL(12,2),
    frais_notaire DECIMAL(12,2),
    frais_agence DECIMAL(12,2),
    cout_travaux DECIMAL(12,2),
    date_signature_prevue DATE,
    notes TEXT,
    documents JSON,
    date_mise_a_jour TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_acquisitions_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS baux (
    id CHAR(36) NOT NULL PRIMARY KEY,
    bien_id CHAR(36) NOT NULL,
    locataire VARCHAR(255) NOT NULL,
    loyer_mensuel DECIMAL(12,2) NOT NULL,
    charges_recuperables DECIMAL(12,2),
    depot_garantie DECIMAL(12,2),
    mode_paiement VARCHAR(50),
    date_debut DATE NOT NULL,
    date_fin DATE,
    condition_paiement TEXT,
    echeance VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_baux_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loyers (
    id CHAR(36) NOT NULL PRIMARY KEY,
    bail_id CHAR(36) NOT NULL,
    mois CHAR(7) NOT NULL,
    montant DECIMAL(12,2) NOT NULL,
    statut VARCHAR(50),
    date_reglement DATE,
    methode_paiement VARCHAR(50),
    penalites DECIMAL(12,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_loyers_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE,
    UNIQUE KEY uq_bail_mois (bail_id, mois)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_tickets (
    id CHAR(36) NOT NULL PRIMARY KEY,
    bien_id CHAR(36) NOT NULL,
    description TEXT NOT NULL,
    priorite VARCHAR(50),
    statut VARCHAR(50),
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_due DATETIME,
    prestataire VARCHAR(255),
    cout_estime DECIMAL(12,2),
    pieces_jointes JSON,
    CONSTRAINT fk_maintenance_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS taches (
    id CHAR(36) NOT NULL PRIMARY KEY,
    categorie VARCHAR(50),
    description TEXT NOT NULL,
    statut VARCHAR(50),
    responsable VARCHAR(50),
    priorite VARCHAR(50),
    echeance DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS documents (
    id CHAR(36) NOT NULL PRIMARY KEY,
    type VARCHAR(100),
    url TEXT,
    statut VARCHAR(50),
    liaison_bien CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS webhooks (
    id CHAR(36) NOT NULL PRIMARY KEY,
    event VARCHAR(100) NOT NULL,
    target_url TEXT NOT NULL,
    secret VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
