-- SQL migration: initial schema and indices
-- Run this on your MySQL/MariaDB database

CREATE TABLE IF NOT EXISTS access_codes (
  code VARCHAR(20) PRIMARY KEY,
  used_by VARCHAR(128) NULL,
  date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  date_used TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS active_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(20) NOT NULL,
  fingerprint VARCHAR(128) NULL,
  session_id VARCHAR(128) NOT NULL,
  last_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_code_session (code, session_id),
  INDEX idx_last_activity (last_activity),
  CONSTRAINT fk_active_sessions_code FOREIGN KEY (code)
    REFERENCES access_codes(code) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Enforce single active session per code at the DB level (optional; uncomment to enforce strictly)
-- ALTER TABLE active_sessions ADD UNIQUE KEY uq_single_per_code (code);

-- Helpful view for admin (optional)
-- CREATE OR REPLACE VIEW v_codes AS
--   SELECT ac.code,
--          (SELECT COUNT(*) FROM active_sessions s WHERE s.code = ac.code) AS active_count,
--          (SELECT MAX(last_activity) FROM active_sessions s WHERE s.code = ac.code) AS last_activity
--   FROM access_codes ac;
