-- Upgrade Bochat Attendance v3 to Commercial v4.
-- BACK UP DATABASE + uploads first. Run ONCE against the v3 schema supplied with this package.
SET NAMES utf8mb4;
SET time_zone = '+03:00';
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE organizations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(150) NOT NULL, code VARCHAR(30) NOT NULL,
  email VARCHAR(150) NULL, phone VARCHAR(50) NULL, logo_path VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_org_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branches (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL,
  code VARCHAR(30) NOT NULL, address VARCHAR(255) NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'Africa/Nairobi',
  active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_branch_org_code (organization_id,code), KEY idx_branch_org_active (organization_id,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE departments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), KEY idx_dept_scope (organization_id,branch_id,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NULL, name VARCHAR(80) NOT NULL,
  slug VARCHAR(80) NOT NULL, description VARCHAR(255) NULL, is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id), UNIQUE KEY uq_role_org_slug (organization_id,slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, permission_key VARCHAR(100) NOT NULL, name VARCHAR(120) NOT NULL,
  category VARCHAR(60) NOT NULL, PRIMARY KEY(id), UNIQUE KEY uq_permission_key(permission_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  role_id INT UNSIGNED NOT NULL, permission_id INT UNSIGNED NOT NULL, PRIMARY KEY(role_id,permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_branch_access (
  user_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL, PRIMARY KEY(user_id,branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE kiosk_devices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NOT NULL, branch_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL, device_code VARCHAR(50) NOT NULL, access_key_hash CHAR(64) NOT NULL,
  auth_version INT UNSIGNED NOT NULL DEFAULT 1, active TINYINT(1) NOT NULL DEFAULT 1, last_seen_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_device_code(device_code), KEY idx_device_scope(organization_id,branch_id,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, organization_id INT UNSIGNED NULL, branch_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(80) NULL, entity_id VARCHAR(80) NULL,
  description VARCHAR(500) NULL, ip_address VARCHAR(45) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), KEY idx_audit_scope_created(organization_id,branch_id,created_at), KEY idx_audit_user_created(user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_settings (
  organization_id INT UNSIGNED NOT NULL, setting_key VARCHAR(100) NOT NULL, setting_value TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(organization_id,setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO organizations (id,name,code,active) VALUES (1,'Default Organization','DEFAULT',1);
INSERT INTO branches (id,organization_id,name,code,timezone,active) VALUES (1,1,'Head Office','HQ','Africa/Nairobi',1);

ALTER TABLE staff
  ADD organization_id INT UNSIGNED NULL AFTER id,
  ADD branch_id INT UNSIGNED NULL AFTER organization_id,
  ADD department_id INT UNSIGNED NULL AFTER branch_id,
  ADD employee_no VARCHAR(50) NULL AFTER department_id,
  ADD email VARCHAR(150) NULL AFTER name,
  ADD phone VARCHAR(50) NULL AFTER email,
  ADD job_title VARCHAR(100) NULL AFTER phone,
  ADD updated_at DATETIME NULL AFTER created_at;
UPDATE staff SET organization_id=1, branch_id=1 WHERE organization_id IS NULL OR branch_id IS NULL;
ALTER TABLE staff MODIFY organization_id INT UNSIGNED NOT NULL, MODIFY branch_id INT UNSIGNED NOT NULL;
CREATE INDEX idx_staff_scope_active_name ON staff(organization_id,branch_id,active,name);

ALTER TABLE attendance_logs
  ADD organization_id INT UNSIGNED NULL AFTER id,
  ADD branch_id INT UNSIGNED NULL AFTER organization_id,
  ADD device_ref_id INT UNSIGNED NULL AFTER staff_id,
  CHANGE device_id device_code VARCHAR(50) NOT NULL;
UPDATE attendance_logs SET organization_id=1, branch_id=1 WHERE organization_id IS NULL OR branch_id IS NULL;
ALTER TABLE attendance_logs MODIFY organization_id INT UNSIGNED NOT NULL, MODIFY branch_id INT UNSIGNED NOT NULL;
CREATE INDEX idx_att_scope_created ON attendance_logs(organization_id,branch_id,created_at);

ALTER TABLE admin_users
  ADD organization_id INT UNSIGNED NULL AFTER id,
  ADD primary_branch_id INT UNSIGNED NULL AFTER organization_id,
  ADD role_id INT UNSIGNED NULL AFTER primary_branch_id,
  ADD name VARCHAR(120) NULL AFTER role_id,
  ADD email VARCHAR(150) NULL AFTER username,
  ADD active TINYINT(1) NOT NULL DEFAULT 1 AFTER password_hash,
  ADD last_login_at DATETIME NULL AFTER active,
  ADD updated_at DATETIME NULL AFTER created_at;
UPDATE admin_users SET organization_id=1, primary_branch_id=1, name=COALESCE(NULLIF(name,''),username) WHERE organization_id IS NULL;

INSERT INTO permissions (permission_key,name,category) VALUES
('dashboard.view','View dashboard','Dashboard'),('attendance.view','View attendance','Attendance'),('attendance.export','Export attendance','Attendance'),
('staff.view','View staff','Staff'),('staff.manage','Manage staff','Staff'),('branches.view','View branches','Organization'),('branches.manage','Manage branches','Organization'),
('devices.view','View kiosk devices','Organization'),('devices.manage','Manage kiosk devices','Organization'),('users.view','View users','Security'),('users.manage','Manage users','Security'),
('roles.view','View roles & permissions','Security'),('roles.manage','Manage roles & permissions','Security'),('reports.view','View reports','Reports'),
('audit.view','View audit trail','Security'),('account.manage','Manage own account','Security');
INSERT INTO roles (id,organization_id,name,slug,description,is_system) VALUES
(1,NULL,'Super Administrator','super_admin','Full platform access across organizations and branches',1),
(2,1,'Organization Administrator','org_admin','Full access inside the organization',1),
(3,1,'Branch Manager','branch_manager','Operational access to assigned branches',1),
(4,1,'HR / Attendance Manager','hr_manager','Manage staff and attendance across assigned branches',1),
(5,1,'Supervisor','supervisor','View operational attendance in assigned branches',1),
(6,1,'Auditor / Viewer','auditor','Read-only attendance, reports and audit access',1);
INSERT INTO role_permissions(role_id,permission_id) SELECT 1,id FROM permissions;
INSERT INTO role_permissions(role_id,permission_id) SELECT 2,id FROM permissions;
INSERT INTO role_permissions(role_id,permission_id) SELECT 3,id FROM permissions WHERE permission_key IN ('dashboard.view','attendance.view','attendance.export','staff.view','staff.manage','devices.view','reports.view','account.manage');
INSERT INTO role_permissions(role_id,permission_id) SELECT 4,id FROM permissions WHERE permission_key IN ('dashboard.view','attendance.view','attendance.export','staff.view','staff.manage','reports.view','account.manage');
INSERT INTO role_permissions(role_id,permission_id) SELECT 5,id FROM permissions WHERE permission_key IN ('dashboard.view','attendance.view','staff.view','reports.view','account.manage');
INSERT INTO role_permissions(role_id,permission_id) SELECT 6,id FROM permissions WHERE permission_key IN ('dashboard.view','attendance.view','attendance.export','staff.view','reports.view','audit.view','account.manage');
UPDATE admin_users SET role_id=1 WHERE role_id IS NULL;
ALTER TABLE admin_users MODIFY role_id INT UNSIGNED NOT NULL, MODIFY name VARCHAR(120) NOT NULL;
INSERT IGNORE INTO user_branch_access(user_id,branch_id) SELECT id,1 FROM admin_users;

INSERT INTO kiosk_devices(id,organization_id,branch_id,name,device_code,access_key_hash,active)
VALUES(1,1,1,'Head Office Kiosk','HQ-KIOSK-01',SHA2('ChangeThisKioskKey123!',256),1);
UPDATE attendance_logs SET device_ref_id=1 WHERE device_ref_id IS NULL;

-- Rename legacy compatibility column into commercial FK column.
ALTER TABLE attendance_logs CHANGE device_ref_id device_id INT UNSIGNED NULL;
CREATE INDEX idx_att_device_ref_created ON attendance_logs(device_id, created_at);


-- Commercial parity indexes and referential integrity.
CREATE UNIQUE INDEX uq_staff_branch_employee_no ON staff(branch_id, employee_no);
CREATE UNIQUE INDEX uq_admin_email ON admin_users(email);
CREATE INDEX idx_admin_org_active ON admin_users(organization_id, active);

ALTER TABLE branches
  ADD CONSTRAINT fk_v4_branch_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE departments
  ADD CONSTRAINT fk_v4_dept_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_dept_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE roles
  ADD CONSTRAINT fk_v4_role_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE role_permissions
  ADD CONSTRAINT fk_v4_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  ADD CONSTRAINT fk_v4_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE;
ALTER TABLE admin_users
  ADD CONSTRAINT fk_v4_admin_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_admin_branch FOREIGN KEY (primary_branch_id) REFERENCES branches(id) ON UPDATE CASCADE ON DELETE SET NULL,
  ADD CONSTRAINT fk_v4_admin_role FOREIGN KEY (role_id) REFERENCES roles(id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE user_branch_access
  ADD CONSTRAINT fk_v4_uba_user FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
  ADD CONSTRAINT fk_v4_uba_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE;
ALTER TABLE staff
  ADD CONSTRAINT fk_v4_staff_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_staff_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_staff_department FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE kiosk_devices
  ADD CONSTRAINT fk_v4_device_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_device_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE attendance_logs
  ADD CONSTRAINT fk_v4_att_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_att_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT fk_v4_att_device FOREIGN KEY (device_id) REFERENCES kiosk_devices(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE audit_logs
  ADD CONSTRAINT fk_v4_audit_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_v4_audit_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_v4_audit_user FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE SET NULL;
ALTER TABLE app_settings
  ADD CONSTRAINT fk_v4_setting_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

SET FOREIGN_KEY_CHECKS=1;