-- ============================================================
-- AB BLOCKS (CodeIgniter 4) - one-time database setup
-- Run in phpMyAdmin: select your database > Import (or SQL tab).
-- SAFE: only ADDS 2 new tables and some speed-up indexes.
-- It does not change or delete any existing table or row.
-- Safe to run twice (it skips what already exists).
-- ============================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(60) NOT NULL,
  `name` varchar(120) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT NULL DEFAULT 'staff',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `must_change` tinyint(1) NOT NULL DEFAULT 0,
  `failed_logins` int(11) NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `action` varchar(60) NOT NULL,
  `detail` text DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Speed-up indexes (reports stay fast with 79,000+ sales rows)
CREATE INDEX IF NOT EXISTS `idx_sales_date`     ON `sales` (`date`);
CREATE INDEX IF NOT EXISTS `idx_sales_stock`    ON `sales` (`stock_category`(30), `stock_name`(60), `stock_type`(60), `status`(10));
CREATE INDEX IF NOT EXISTS `idx_sales_customer` ON `sales` (`customer_name`(60), `date`);
CREATE INDEX IF NOT EXISTS `idx_ledger_acc`     ON `ledger_entry` (`new_ledger_acc_id`, `ledger_entry_id`);
CREATE INDEX IF NOT EXISTS `idx_ledger_date`    ON `ledger_entry` (`date`);
CREATE INDEX IF NOT EXISTS `idx_cement_lookup`  ON `cement_stock` (`identity`(20), `status`(10), `product_name`(60), `cement_type`(40));
CREATE INDEX IF NOT EXISTS `idx_nla_desc`       ON `new_ledger_acc` (`description`(30), `status`(10));

-- First administrator. Username: admin   (temporary password is given to you separately;
-- you are forced to choose your own at first login).
INSERT INTO `users` (`username`,`name`,`password_hash`,`role`,`active`,`must_change`,`created_at`,`updated_at`)
SELECT 'admin','Administrator',
'$2y$10$Okm9SSIdOIHyRNtlyM5lEuyHMEVS0OF7t1.yYID.8CHfitOk0UzFq',
'admin',1,1,NOW(),NOW() FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `username`='admin');