-- #migration 2026_08_10_183_contacts

CREATE TABLE IF NOT EXISTS contacts (
    id           VARCHAR(36)  NOT NULL PRIMARY KEY DEFAULT (UUID()),
    user_id      VARCHAR(36)  NOT NULL,
    nom          VARCHAR(200) NOT NULL,
    moyen        VARCHAR(50)  NULL,  -- email|tel|instagram|linkedin|autre
    valeur       VARCHAR(300) NULL,  -- valeur du moyen de contact
    notes        TEXT         NULL,
    url          VARCHAR(500) NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contact_roles (
    id           VARCHAR(36)  NOT NULL PRIMARY KEY DEFAULT (UUID()),
    contact_id   VARCHAR(36)  NOT NULL,
    role         VARCHAR(50)  NOT NULL,  -- prospection|reseau
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contact_role (contact_id, role),
    INDEX idx_contact (contact_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Entrées cumulables liées à une action (suivi structuré)
CREATE TABLE IF NOT EXISTS action_entries (
    id           VARCHAR(36)  NOT NULL PRIMARY KEY DEFAULT (UUID()),
    user_id      VARCHAR(36)  NOT NULL,
    card_id      VARCHAR(20)  NOT NULL,
    entry_type   VARCHAR(50)  NOT NULL,  -- site_inspiration|contact|concurrent
    contact_id   VARCHAR(36)  NULL,      -- lien vers contacts si applicable
    data_json    JSON         NOT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_card (user_id, card_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Images liées aux entrées (C-131 inspiration sites)
CREATE TABLE IF NOT EXISTS action_entry_images (
    id           VARCHAR(36)  NOT NULL PRIMARY KEY DEFAULT (UUID()),
    entry_id     VARCHAR(36)  NOT NULL,
    url          VARCHAR(500) NOT NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entry (entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;