-- ============================================================
-- CES Portal — Database Schema
-- Connecticut Elderly Services Training Portal
-- ============================================================
-- HOW TO USE:
--   1. Log in to cPanel → phpMyAdmin
--   2. Select your database (e.g. connecticutelder_cesportal)
--   3. Click the "SQL" tab
--   4. Paste this entire file and click "Go"
--   5. Then run the INSERT at the bottom to create your first admin account
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

-- ── agencies ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `agencies` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(200)     NOT NULL,
    `contact_email` VARCHAR(200)     NULL DEFAULT NULL,
    `phone`         VARCHAR(30)      NULL DEFAULT NULL,
    `address`       VARCHAR(500)     NULL DEFAULT NULL,
    `notes`         TEXT             NULL DEFAULT NULL,
    `active`        TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ux_agencies_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── users ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `agency_id`     INT UNSIGNED     NULL DEFAULT NULL,
    `name`          VARCHAR(200)     NOT NULL,
    `username`      VARCHAR(80)      NOT NULL,
    `email`         VARCHAR(200)     NOT NULL,
    `password_hash` VARCHAR(255)     NOT NULL,
    `role`          ENUM('super_admin','agency_user') NOT NULL DEFAULT 'agency_user',
    `active`        TINYINT(1)       NOT NULL DEFAULT 1,
    `last_login`    DATETIME         NULL DEFAULT NULL,
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ux_users_username` (`username`),
    UNIQUE KEY `ux_users_email` (`email`),
    KEY `fk_users_agency` (`agency_id`),
    CONSTRAINT `fk_users_agency`
        FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── learners ──────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `learners` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `agency_id`     INT UNSIGNED     NOT NULL,
    `first_name`    VARCHAR(100)     NOT NULL,
    `last_name`     VARCHAR(100)     NOT NULL,
    `email`         VARCHAR(200)     NULL DEFAULT NULL,
    `phone`         VARCHAR(30)      NULL DEFAULT NULL,
    `date_of_birth` DATE             NULL DEFAULT NULL,
    `notes`         TEXT             NULL DEFAULT NULL,
    `active`        TINYINT(1)       NOT NULL DEFAULT 1,
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_learners_agency` (`agency_id`),
    CONSTRAINT `fk_learners_agency`
        FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── training_progress ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `training_progress` (
    `id`              INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `learner_id`      INT UNSIGNED   NOT NULL,
    `module_number`   TINYINT        NOT NULL,
    `module_name`     VARCHAR(200)   NULL DEFAULT NULL,
    `status`          ENUM('not_started','in_progress','completed') NOT NULL DEFAULT 'not_started',
    `score`           DECIMAL(5,2)   NULL DEFAULT NULL COMMENT 'Percentage score 0.00–100.00',
    `completed_at`    DATETIME       NULL DEFAULT NULL,
    `created_at`      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ux_progress_learner_module` (`learner_id`, `module_number`),
    KEY `fk_progress_learner` (`learner_id`),
    CONSTRAINT `fk_progress_learner`
        FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── exam_attempts ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `exam_attempts` (
    `id`              INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `learner_id`      INT UNSIGNED   NOT NULL,
    `attempt_number`  TINYINT        NOT NULL DEFAULT 1,
    `score`           DECIMAL(5,2)   NULL DEFAULT NULL,
    `passed`          TINYINT(1)     NOT NULL DEFAULT 0,
    `attempted_at`    DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_exam_learner` (`learner_id`),
    CONSTRAINT `fk_exam_learner`
        FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── certificates ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `certificates` (
    `id`                 INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `learner_id`         INT UNSIGNED  NOT NULL,
    `certificate_number` VARCHAR(80)   NOT NULL,
    `issued_date`        DATE          NOT NULL,
    `expiry_date`        DATE          NULL DEFAULT NULL,
    `notes`              TEXT          NULL DEFAULT NULL,
    `created_at`         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ux_certificates_number` (`certificate_number`),
    KEY `fk_certs_learner` (`learner_id`),
    CONSTRAINT `fk_certs_learner`
        FOREIGN KEY (`learner_id`) REFERENCES `learners` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── audit_logs ────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id`          INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `user_id`     INT UNSIGNED   NULL DEFAULT NULL COMMENT 'NULL for system events',
    `action`      VARCHAR(80)    NOT NULL COMMENT 'e.g. user.login, agency.create',
    `description` VARCHAR(500)   NOT NULL,
    `target_id`   INT UNSIGNED   NULL DEFAULT NULL,
    `target_type` VARCHAR(40)    NULL DEFAULT NULL,
    `ip_address`  VARCHAR(45)    NULL DEFAULT NULL,
    `created_at`  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_user`   (`user_id`),
    KEY `idx_audit_action` (`action`),
    KEY `idx_audit_date`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── login_attempts (brute-force throttling) ───────────────────────────────────

CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_address`   VARCHAR(45)  NOT NULL,
    `attempted_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_attempts_ip_time` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NO DEFAULT CREDENTIALS ARE SEEDED.
-- After running this SQL, visit:  yoursite.com/setup.php
-- That page lets you create the first Super Admin account.
-- It permanently disables itself once one Super Admin exists.
-- ============================================================

SET foreign_key_checks = 1;
