-- ============================================================
-- COMARNET DATABASE SETUP SCRIPT
-- ============================================================
-- This script creates all necessary tables for the Comarnet app
-- Run this after creating the database in cPanel
-- ============================================================

-- Users Table (for admin authentication)
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `openId` varchar(64) NOT NULL UNIQUE,
  `name` text,
  `email` varchar(320),
  `loginMethod` varchar(64),
  `passwordHash` varchar(255),
  `role` enum('user','admin') NOT NULL DEFAULT 'user',
  `failedLoginAttempts` int DEFAULT 0,
  `lockedUntil` timestamp NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastSignedIn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_openId` (`openId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Members Table
CREATE TABLE IF NOT EXISTS `teamMembers` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL,
  `department` varchar(255) DEFAULT 'General',
  `bio` text,
  `imageUrl` varchar(500),
  `email` varchar(320),
  `phone` varchar(20),
  `isExecutive` tinyint(1) DEFAULT 0,
  `displayOrder` int DEFAULT 0,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_isExecutive` (`isExecutive`),
  INDEX `idx_displayOrder` (`displayOrder`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SAMPLE DATA (Optional - remove if not needed)
-- ============================================================

-- Insert sample admin user (password: Admin@123456)
-- Note: You should create your own admin account via the login page
-- This is just for reference
INSERT INTO `users` (
  `openId`,
  `name`,
  `email`,
  `loginMethod`,
  `passwordHash`,
  `role`,
  `failedLoginAttempts`,
  `lastSignedIn`
) VALUES (
  'admin-001',
  'Admin User',
  'admin@comarnet.com',
  'email',
  '$2b$12$placeholder_hash_replace_with_real_hash',
  'admin',
  0,
  NOW()
) ON DUPLICATE KEY UPDATE `email`=`email`;

-- Insert sample team members
INSERT INTO `teamMembers` (
  `name`,
  `role`,
  `department`,
  `bio`,
  `email`,
  `phone`,
  `isExecutive`,
  `displayOrder`
) VALUES
(
  'John Doe',
  'Chief Executive Officer',
  'Executive',
  'Leading Comarnet with 20+ years of IT industry experience.',
  'john.doe@comarnet.com',
  '+60-1-2158-8805',
  1,
  1
),
(
  'Jane Smith',
  'Chief Technology Officer',
  'Technology',
  'Driving innovation in network infrastructure and cloud solutions.',
  'jane.smith@comarnet.com',
  '+60-1-2158-8806',
  1,
  2
),
(
  'Mike Johnson',
  'Head of Sales',
  'Sales',
  'Building strong partnerships with enterprise clients across APAC.',
  'mike.johnson@comarnet.com',
  '+60-1-2158-8807',
  0,
  3
),
(
  'Sarah Williams',
  'Support Manager',
  'Support',
  'Ensuring 24/7 customer support excellence.',
  'sarah.williams@comarnet.com',
  '+60-1-2158-8808',
  0,
  4
);

-- ============================================================
-- VERIFICATION QUERIES
-- ============================================================

-- Check if tables were created successfully
SELECT 'Users table created' as status FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
UNION ALL
SELECT 'Team Members table created' as status FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'teamMembers';

-- Count records
SELECT 'Users count: ' as metric, COUNT(*) as value FROM users
UNION ALL
SELECT 'Team Members count: ' as metric, COUNT(*) as value FROM teamMembers;