-- ============================================================
-- Module: Maintenance FTTH B2B
-- ============================================================

-- Tickets importés depuis Excel ou saisis manuellement
CREATE TABLE IF NOT EXISTS maintenance_ftth_tickets (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    ref_code        VARCHAR(80)  NULL COMMENT 'Référence ticket Excel',
    client_name     VARCHAR(255) NOT NULL,
    client_code     VARCHAR(80)  NULL,
    client_phone    VARCHAR(60)  NULL,
    client_email    VARCHAR(150) NULL,
    client_address  TEXT         NULL,
    client_lat      DECIMAL(10,7) NULL,
    client_lng      DECIMAL(10,7) NULL,
    site_name       VARCHAR(255) NULL,
    nature_intervention VARCHAR(300) NULL,
    description     TEXT         NULL,
    priority        ENUM('Basse','Moyenne','Haute','Urgent') NOT NULL DEFAULT 'Moyenne',
    status          ENUM('nouveau','assigné','en_cours','traité','validé','clôturé') NOT NULL DEFAULT 'nouveau',
    imported_at     DATETIME     NULL,
    imported_by     INT          NULL,
    created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_client (client_name),
    FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assignation des tickets aux techniciens terrain
CREATE TABLE IF NOT EXISTS maintenance_ftth_assignments (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id       INT          NOT NULL,
    technician_id   INT          NOT NULL,
    assigned_by     INT          NOT NULL,
    assigned_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    notes           TEXT         NULL,
    notified_email  TINYINT(1)   NOT NULL DEFAULT 0,
    notified_app    TINYINT(1)   NOT NULL DEFAULT 0,
    INDEX idx_ticket (ticket_id),
    INDEX idx_tech   (technician_id),
    FOREIGN KEY (ticket_id)     REFERENCES maintenance_ftth_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (technician_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by)   REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fiche terrain digitalisée (4 onglets)
CREATE TABLE IF NOT EXISTS maintenance_ftth_reports (
    id                      INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id               INT          NOT NULL UNIQUE,
    technician_id           INT          NOT NULL,

    -- En-tête de la fiche
    operation_type          ENUM('maintenance','travaux','autres') NOT NULL DEFAULT 'maintenance',
    intervention_date       DATE         NULL,
    intervention_start      TIME         NULL,
    intervention_end        TIME         NULL,

    -- Onglet 1 : Constat
    constat_comments        TEXT         NULL COMMENT 'Commentaire général avant intervention',
    impacted_equipment      JSON         NULL COMMENT '[{"name":"...", "photo_path":"...", "comment":"..."}]',

    -- Onglet 2 : Intervention
    materials_used          JSON         NULL COMMENT '[{"name":"...", "qty":1, "ref":"...", "photo_path":"...", "comment":"..."}]',
    general_comments        TEXT         NULL,

    -- Onglet 3 : Appréciation client
    satisfaction_service    ENUM('très_satisfait','satisfait','peu_satisfait','non_satisfait') NULL,
    satisfaction_delay      ENUM('très_satisfait','satisfait','peu_satisfait','non_satisfait') NULL,
    satisfaction_quality    ENUM('très_satisfait','satisfait','peu_satisfait','non_satisfait') NULL,
    satisfaction_behavior   ENUM('très_satisfait','satisfait','peu_satisfait','non_satisfait') NULL,
    client_name_onsite      VARCHAR(200) NULL,
    client_remarks          TEXT         NULL,
    service_restored        TINYINT(1)   NOT NULL DEFAULT 0,

    -- Onglet 4 : Signatures
    client_signature_path   VARCHAR(350) NULL,
    agent_signature_path    VARCHAR(350) NULL,

    -- GPS de l'agent au moment de la soumission (confirmation position)
    submit_gps_lat          DECIMAL(10,7) NULL,
    submit_gps_lng          DECIMAL(10,7) NULL,

    -- Workflow
    status                  ENUM('brouillon','soumis','validé','rejeté') NOT NULL DEFAULT 'brouillon',
    submitted_at            DATETIME     NULL,
    validated_at            DATETIME     NULL,
    validated_by            INT          NULL,
    rejection_reason        TEXT         NULL,

    created_at              TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_ticket    (ticket_id),
    INDEX idx_tech      (technician_id),
    INDEX idx_status    (status),
    FOREIGN KEY (ticket_id)     REFERENCES maintenance_ftth_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (technician_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (validated_by)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Photos attachées à la fiche (onglet constat + onglet intervention)
CREATE TABLE IF NOT EXISTS maintenance_ftth_photos (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    report_id       INT          NOT NULL,
    tab_type        ENUM('constat','intervention') NOT NULL,
    item_index      SMALLINT     NULL COMMENT 'Index de l équipement/matériel associé dans le JSON',
    photo_path      VARCHAR(350) NOT NULL,
    comment         TEXT         NULL,
    gps_lat         DECIMAL(10,7) NULL,
    gps_lng         DECIMAL(10,7) NULL,
    taken_at        DATETIME     NULL,
    created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_report (report_id),
    FOREIGN KEY (report_id) REFERENCES maintenance_ftth_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;