CREATE DATABASE IF NOT EXISTS TESVISCO_ADMIN
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE TESVISCO_ADMIN;

CREATE TABLE IF NOT EXISTS stores (
  id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(8) NOT NULL UNIQUE,
  label VARCHAR(80) NOT NULL,
  db_name VARCHAR(120) NOT NULL UNIQUE,
  frontend_url VARCHAR(255) NOT NULL,
  accent_color VARCHAR(16) NOT NULL DEFAULT '#16a34a',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO stores (code, label, db_name, frontend_url, accent_color) VALUES
  ('A', 'Elroi Foods',       'TESVISCOVENTURES', 'http://localhost:5173', '#16a34a'),
  ('B', 'African Groceries', 'african_groceries', 'http://localhost:5175', '#c2410c')
ON DUPLICATE KEY UPDATE
  label = VALUES(label), db_name = VALUES(db_name),
  frontend_url = VALUES(frontend_url), accent_color = VALUES(accent_color);

CREATE TABLE IF NOT EXISTS super_admins (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(191) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  last_store_code CHAR(1) NOT NULL DEFAULT 'A',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS super_admin_stores (
  super_admin_id INT UNSIGNED NOT NULL,
  store_id TINYINT UNSIGNED NOT NULL,
  store_admin_user_id BIGINT UNSIGNED NULL,
  store_admin_role_id INT UNSIGNED NULL,
  PRIMARY KEY (super_admin_id, store_id),
  FOREIGN KEY (super_admin_id) REFERENCES super_admins(id) ON DELETE CASCADE,
  FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notification_reads (
  super_admin_id INT UNSIGNED NOT NULL,
  store_id TINYINT UNSIGNED NOT NULL,
  notification_key VARCHAR(120) NOT NULL,
  read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (super_admin_id, store_id, notification_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  super_admin_id INT UNSIGNED NULL,
  store_id TINYINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  target_type VARCHAR(60) NOT NULL DEFAULT '',
  target_id VARCHAR(80) NOT NULL DEFAULT '',
  ip VARCHAR(45) NOT NULL DEFAULT '',
  meta JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_store (store_id, created_at),
  INDEX idx_audit_admin (super_admin_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS rate_limits (
  bucket_key VARCHAR(191) NOT NULL,
  window_start DATETIME NOT NULL,
  hits INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (bucket_key, window_start)
) ENGINE=InnoDB;