-- Attendance Management System — Database Schema (MySQL)

CREATE TABLE tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    subdomain VARCHAR(100) NOT NULL UNIQUE,
    logo_path VARCHAR(255) NULL,
    status ENUM('active','suspended','trial') NOT NULL DEFAULT 'trial',
    settings JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE domains (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    domain VARCHAR(191) NOT NULL UNIQUE,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    verified_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NULL, -- NULL = Super Admin (platform-level account)
    unique_id VARCHAR(50) NOT NULL UNIQUE,      -- printed on badge, e.g. STJ-2026-0001
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin','admin','staff','student') NOT NULL,
    photo_path VARCHAR(255) NULL,
    bio_data JSON NULL,                          -- dob, class/dept, phone, address, etc.
    badge_token VARCHAR(100) NOT NULL UNIQUE,     -- encoded in the QR/barcode on the badge
    status ENUM('active','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    license_key VARCHAR(50) NOT NULL UNIQUE,
    plan ENUM('trial','standard','premium') NOT NULL DEFAULT 'standard',
    seats_allowed INT NOT NULL DEFAULT 100,
    features_enabled JSON NULL,                   -- ["barcode","sms_alerts","addon:payroll"]
    status ENUM('pending','active','expired','revoked') NOT NULL DEFAULT 'pending',
    activated_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE attendance_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,                   -- "Main Gate", "Training Hall B"
    type ENUM('entry','exit','both') NOT NULL DEFAULT 'both',
    device_id VARCHAR(100) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    geofence_radius_m INT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE attendance_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    user_id INT NOT NULL,
    location_id INT NOT NULL,
    clock_in_at DATETIME NULL,
    clock_out_at DATETIME NULL,
    duration_minutes INT NULL,
    status ENUM('open','closed','auto_closed') NOT NULL DEFAULT 'open',
    is_late TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES attendance_locations(id) ON DELETE CASCADE,
    INDEX idx_tenant_user_date (tenant_id, user_id, clock_in_at)
) ENGINE=InnoDB;

CREATE TABLE addons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    version VARCHAR(20) NOT NULL,
    description TEXT NULL,
    is_approved TINYINT(1) NOT NULL DEFAULT 0,     -- Super Admin gatekeeping
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE tenant_addons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    addon_id INT NOT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    config JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_addon (tenant_id, addon_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed the Super Admin (change password immediately after first login)
-- Password hash below is for 'ChangeMe123!' — generate your own with password_hash()
-- INSERT INTO users (tenant_id, unique_id, name, email, password_hash, role, badge_token)
-- VALUES (NULL, 'SUPER-0001', 'Platform Super Admin', 'super@yourapp.com', '$2y$10$...', 'super_admin', 'seed-badge-token-0001');

-- ============================================================
-- Site customization (added for Super Admin site settings + menu
-- builder, and per-tenant environment customization). Run this block
-- against an existing database if you already ran the schema above.
-- ============================================================

CREATE TABLE platform_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value LONGTEXT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE menus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- tenants.settings (already defined in the CREATE TABLE tenants above)
-- is a JSON column and is reused for per-tenant customization:
-- { "logo_path": "...", "primary_color": "#1f4fd8", "welcome_html": "<p>...</p>", "tagline": "..." }
-- No migration needed for that — it's already part of the tenants table.