-- ============================================================
-- SEYALIX / SEDER CORE - MASTER (CONTROL) DATABASE
-- One central database that keeps track of every company (tenant).
-- Each company still gets its OWN separate database (full isolation).
-- This master DB is ONLY used to:
--   1. Let the Super Admin create/manage companies
--   2. Route a login (email) to the correct company database
--
-- HOW TO SET THIS UP (shared/cPanel hosting):
--   Your regular MySQL user cannot run CREATE DATABASE (hosting
--   restricts this on purpose), so the line below is just a reference.
--   Instead:
--     1. cPanel -> MySQL Databases -> create a database named
--        "seyalix_master" (cPanel will prefix it, e.g.
--        adrieltech_seyalix_master)
--     2. cPanel -> MySQL Databases -> create a database user, add it
--        to that database with ALL PRIVILEGES
--     3. Open phpMyAdmin -> select that new database -> Import ->
--        upload this file (skip/ignore the CREATE DATABASE/USE lines,
--        or just run everything from CREATE TABLE downward)
--     4. Put those exact credentials into config/master_database.php
-- ============================================================

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

-- ------------------------------------------------------------
-- 1. SUPER ADMINS (you / your team - manage all companies)
-- ------------------------------------------------------------
CREATE TABLE super_admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ------------------------------------------------------------
-- 2. TENANTS (companies). Each row = one company = one separate DB.
-- ------------------------------------------------------------
CREATE TABLE tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL,
    company_code VARCHAR(20) UNIQUE NOT NULL,     -- short reference code, e.g. ABC001 (not used to login, just for your reference)
    db_host VARCHAR(100) NOT NULL DEFAULT 'localhost',
    db_name VARCHAR(64) NOT NULL,
    db_user VARCHAR(64) NOT NULL,
    db_pass VARCHAR(255) NOT NULL,                 -- stored so the app can auto-connect on login
    status ENUM('active','suspended') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ------------------------------------------------------------
-- 3. LOGIN INDEX - maps an email to which company DB it belongs to.
--    Kept in sync automatically whenever a company admin creates/edits/
--    deletes an employee login inside their own company database.
-- ------------------------------------------------------------
CREATE TABLE tenant_user_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) UNIQUE NOT NULL,
    tenant_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- ------------------------------------------------------------
-- Create your first Super Admin login.
-- IMPORTANT: change this password immediately after first login
-- (default password below is: SuperAdmin@123)
-- ------------------------------------------------------------
INSERT INTO super_admins (name, email, password) VALUES
('Super Admin', 'you@adrieltech.com', '$2b$10$iu/73gLmirG2sQb4qjOlMepdi6ogbfJp8X4KhUm83ErRC5RAWqGMa');
-- ^ this hash corresponds to password: SuperAdmin@123
