CREATE TABLE IF NOT EXISTS departments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, description TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS units (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, department_id INT UNSIGNED NOT NULL, code VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(180) NOT NULL, description TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS features (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id INT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(180) NOT NULL, description TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_no VARCHAR(50) UNIQUE, first_name VARCHAR(100) NOT NULL,last_name VARCHAR(100) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,phone VARCHAR(50),job_title VARCHAR(150),department_id INT UNSIGNED NULL,status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',last_login_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS roles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL UNIQUE,slug VARCHAR(100) NOT NULL UNIQUE,description TEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS permissions (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL UNIQUE,slug VARCHAR(120) NOT NULL UNIQUE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS user_roles (user_id INT UNSIGNED NOT NULL,role_id INT UNSIGNED NOT NULL,PRIMARY KEY(user_id,role_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS role_permissions (role_id INT UNSIGNED NOT NULL,permission_id INT UNSIGNED NOT NULL,PRIMARY KEY(role_id,permission_id),FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200) NOT NULL,body TEXT NOT NULL,scope ENUM('global','department','individual') NOT NULL DEFAULT 'global',target_user_id INT UNSIGNED NULL,department_id INT UNSIGNED NULL,expires_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(target_user_id) REFERENCES users(id) ON DELETE CASCADE,FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE CASCADE,INDEX(scope),INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notification_user (notification_id BIGINT UNSIGNED NOT NULL,user_id INT UNSIGNED NOT NULL,read_at DATETIME NULL,PRIMARY KEY(notification_id,user_id),FOREIGN KEY(notification_id) REFERENCES notifications(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,action VARCHAR(120) NOT NULL,entity VARCHAR(100),entity_id BIGINT NULL,metadata JSON NULL,ip_address VARCHAR(45),user_agent VARCHAR(500),created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(user_id),INDEX(created_at),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS login_sessions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,session_id VARCHAR(128) NOT NULL,login_at DATETIME NOT NULL,last_seen DATETIME NOT NULL,logout_at DATETIME NULL,duration_seconds INT NOT NULL DEFAULT 0,ip_address VARCHAR(45),user_agent VARCHAR(500),INDEX(user_id),INDEX(login_at),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS user_activity (user_id INT UNSIGNED NOT NULL,session_key VARCHAR(128) NOT NULL,last_seen DATETIME NOT NULL,seconds_active BIGINT NOT NULL DEFAULT 0,ip_address VARCHAR(45),PRIMARY KEY(user_id,session_key),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS projects (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200) NOT NULL,status ENUM('planned','active','on_hold','completed','cancelled') NOT NULL DEFAULT 'planned',manager_id INT UNSIGNED NULL,department_id INT UNSIGNED NULL,start_date DATE NULL,end_date DATE NULL,budget DECIMAL(15,2) NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(manager_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS email_queue (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,to_email VARCHAR(190) NOT NULL,to_name VARCHAR(200),subject VARCHAR(255) NOT NULL,body LONGTEXT NOT NULL,status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',attempts INT NOT NULL DEFAULT 0,available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,sent_at DATETIME NULL,last_error TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(status,available_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO roles(name,slug,description) VALUES ('Super Administrator','super-admin','Full system access'),('Executive','executive','Executive dashboards and oversight'),('Department Manager','department-manager','Department management'),('Employee','employee','Standard employee access');
INSERT IGNORE INTO permissions(name,slug) VALUES ('View organization','org.view'),('View employees','employees.view'),('View notifications','notifications.view'),('Manage organization','org.manage'),('Manage users','users.manage'),('Executive dashboard','executive.dashboard'),('Audit access','audit.view'),('Time tracking','time.view');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='super-admin';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.slug IN ('org.view','employees.view','notifications.view','executive.dashboard','audit.view','time.view') WHERE r.slug='executive';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.slug IN ('org.view','employees.view','notifications.view') WHERE r.slug='department-manager';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.slug IN ('org.view','notifications.view') WHERE r.slug='employee';
INSERT IGNORE INTO departments(code,name,description,sort_order) VALUES
('ENGTECH','Engineering & Tech','Software, delivery, cloud, infrastructure and security.',1),
('BIZDEV','Business Development','Sales, marketing, communications, partnerships and proposals.',2),
('FINADMIN','Finance & Administration','Finance, facilities and internal operations.',3),
('HC','HC & Careers','Talent, learning, careers, culture and employee relations.',4),
('LRC','Legal, Risk & Compliance','Legal, risk, audit, compliance and data privacy.',5);
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'SE-RD','Software Engineering (R&D)','Software engineering, research and product development.',1 FROM departments WHERE code='ENGTECH';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'ITSD-PM','IT Services & Delivery (Project Management)','Technology delivery, projects, service management and support.',2 FROM departments WHERE code='ENGTECH';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'CLOUD','Cloud','Cloud platforms, architecture and operations.',3 FROM departments WHERE code='ENGTECH';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'INFSEC','Infrastructure & Security','Infrastructure, networks, endpoint and cybersecurity.',4 FROM departments WHERE code='ENGTECH';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'SALES','Sales & Accounts','Pipeline, account management and customer relationships.',1 FROM departments WHERE code='BIZDEV';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'MKT-COMMS','Marketing & Communications','Brand, campaigns, communications and content.',2 FROM departments WHERE code='BIZDEV';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'PART-PROP','Partnerships & Proposals','Partnership development, tenders and proposals.',3 FROM departments WHERE code='BIZDEV';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'FIN-ACC','Finance & Accounting','Accounting, budgets, billing and financial controls.',1 FROM departments WHERE code='FINADMIN';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'ADMIN-FAC','Administration & Facilities','Facilities, procurement, logistics and office administration.',2 FROM departments WHERE code='FINADMIN';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'INT-OPS','Internal Operations','Internal process coordination and business operations.',3 FROM departments WHERE code='FINADMIN';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'TA','Talent Acquisition','Recruitment, onboarding and workforce planning.',1 FROM departments WHERE code='HC';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'LNC','Learning & Career','Learning, skills, career paths and development.',2 FROM departments WHERE code='HC';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'CER','Culture, Engagement & Employee Relations','Culture, engagement, wellbeing and employee relations.',3 FROM departments WHERE code='HC';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'LEGAL','Legal & Contracts','Legal matters, contract lifecycle and advisory.',1 FROM departments WHERE code='LRC';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'RISK-AUDIT','Risk Management & Internal Audit','Enterprise risk, controls and internal audit.',2 FROM departments WHERE code='LRC';
INSERT IGNORE INTO units(department_id,code,name,description,sort_order) SELECT id,'COMP-DP','Compliance & Data Privacy','Regulatory compliance, privacy and information governance.',3 FROM departments WHERE code='LRC';