-- ===========================================
-- API Centralisee Althea — Schema complet V1
-- ===========================================
-- Base : cbernardalth_api
-- Charset : utf8mb4
-- ===========================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- -------------------------------------------
-- Table : migrations (tracking des migrations)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS migrations (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    filename    VARCHAR(255) NOT NULL UNIQUE,
    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : apps (registry des applications)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS apps (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    slug        VARCHAR(50) UNIQUE NOT NULL,
    name        VARCHAR(100) NOT NULL,
    api_key     VARCHAR(128) NOT NULL,
    api_secret  VARCHAR(128) NOT NULL,
    base_url    VARCHAR(255) NOT NULL,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_api_key (api_key),
    INDEX idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : event_types (catalogue des evenements)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS event_types (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    slug            VARCHAR(100) UNIQUE NOT NULL,
    description     VARCHAR(255),
    source_app_id   INT NOT NULL,
    payload_schema  JSON NULL,
    is_active       TINYINT(1) DEFAULT 1,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_source (source_app_id),
    FOREIGN KEY (source_app_id) REFERENCES apps(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : subscriptions (abonnements)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    app_id          INT NOT NULL,
    event_type_id   INT NOT NULL,
    callback_url    VARCHAR(255) NOT NULL,
    secret          VARCHAR(128) NOT NULL,
    is_active       TINYINT(1) DEFAULT 1,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_app_event (app_id, event_type_id),
    FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE,
    FOREIGN KEY (event_type_id) REFERENCES event_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : events (evenements emis)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS events (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type_id   INT NOT NULL,
    source_app_id   INT NOT NULL,
    idempotency_key VARCHAR(128) NULL,
    payload         JSON NOT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_created (event_type_id, created_at),
    INDEX idx_source_created (source_app_id, created_at),
    INDEX idx_created (created_at),
    UNIQUE KEY uq_source_idempotency (source_app_id, idempotency_key),
    FOREIGN KEY (event_type_id) REFERENCES event_types(id),
    FOREIGN KEY (source_app_id) REFERENCES apps(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : deliveries (livraisons aux abonnes)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS deliveries (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_id        BIGINT NOT NULL,
    subscription_id INT NOT NULL,
    status          ENUM('pending', 'delivered', 'failed', 'abandoned') DEFAULT 'pending',
    attempts        TINYINT UNSIGNED DEFAULT 0,
    max_attempts    TINYINT UNSIGNED DEFAULT 5,
    next_attempt_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_attempt_at DATETIME NULL,
    response_code   SMALLINT UNSIGNED NULL,
    response_body   TEXT NULL,
    delivered_at    DATETIME NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pending (status, next_attempt_at),
    INDEX idx_event (event_id),
    INDEX idx_subscription (subscription_id),
    INDEX idx_status_created (status, created_at),
    FOREIGN KEY (event_id) REFERENCES events(id),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : sso_users (comptes SSO)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS sso_users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    first_name      VARCHAR(100),
    last_name       VARCHAR(100),
    is_active       TINYINT(1) DEFAULT 1,
    token_version   INT UNSIGNED DEFAULT 1,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at   DATETIME NULL,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : sso_app_roles (roles par app)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS sso_app_roles (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    app_id      INT NOT NULL,
    role        VARCHAR(50) NOT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_app (user_id, app_id),
    FOREIGN KEY (user_id) REFERENCES sso_users(id) ON DELETE CASCADE,
    FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : sso_sessions (refresh tokens)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS sso_sessions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    token_hash      VARCHAR(128) NOT NULL,
    ip_address      VARCHAR(45) NULL,
    user_agent      VARCHAR(500) NULL,
    expires_at      DATETIME NOT NULL,
    revoked_at      DATETIME NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_token (token_hash),
    INDEX idx_expires (expires_at),
    FOREIGN KEY (user_id) REFERENCES sso_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : rate_limits (rate limiting)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    action      VARCHAR(50) NOT NULL,
    identifier  VARCHAR(100) NOT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_action_identifier (action, identifier, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : alert_log (historique des alertes)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS alert_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    level       ENUM('warning', 'critical', 'emergency') NOT NULL,
    alert_type  VARCHAR(100) NOT NULL,
    subject     VARCHAR(255) NOT NULL,
    details     TEXT NULL,
    sent_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_sent (alert_type, sent_at),
    INDEX idx_level (level, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- Table : health_checks (resultats monitoring)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS health_checks (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    check_type      VARCHAR(50) NOT NULL,
    status          ENUM('ok', 'warning', 'critical', 'emergency') NOT NULL,
    details         JSON NULL,
    checked_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_checked (check_type, checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;