-- FM ERP patch: work order closure workflow + company_id on shared tables
-- Run once on existing erptech_fm database

ALTER TABLE `work_orders`
  ADD COLUMN IF NOT EXISTS `qa_status` enum('none','pending','approved','rejected') NOT NULL DEFAULT 'none' AFTER `approval_status`,
  ADD COLUMN IF NOT EXISTS `qa_approved_by` int(10) UNSIGNED DEFAULT NULL AFTER `qa_status`,
  ADD COLUMN IF NOT EXISTS `qa_approved_at` datetime DEFAULT NULL AFTER `qa_approved_by`,
  ADD COLUMN IF NOT EXISTS `client_approval_status` enum('none','pending','approved','rejected') NOT NULL DEFAULT 'none' AFTER `qa_approved_at`,
  ADD COLUMN IF NOT EXISTS `client_approved_by` int(10) UNSIGNED DEFAULT NULL AFTER `client_approval_status`,
  ADD COLUMN IF NOT EXISTS `client_approved_at` datetime DEFAULT NULL AFTER `client_approved_by`,
  ADD COLUMN IF NOT EXISTS `invoice_id` int(10) UNSIGNED DEFAULT NULL AFTER `client_approved_at`;

ALTER TABLE `maintenance_requests`
  ADD COLUMN IF NOT EXISTS `company_id` int(10) UNSIGNED DEFAULT NULL AFTER `facility_id`;

UPDATE `maintenance_requests` mr
  JOIN `facilities` f ON f.id = mr.facility_id
  SET mr.company_id = f.company_id
  WHERE mr.company_id IS NULL AND f.company_id IS NOT NULL;

ALTER TABLE `inventory_items`
  ADD COLUMN IF NOT EXISTS `company_id` int(10) UNSIGNED DEFAULT NULL AFTER `id`;

ALTER TABLE `vendors`
  ADD COLUMN IF NOT EXISTS `company_id` int(10) UNSIGNED DEFAULT NULL AFTER `id`;

-- Backfill default company (id=1) when single-tenant
UPDATE `facilities` SET `company_id` = 1 WHERE `company_id` IS NULL LIMIT 1000;
UPDATE `inventory_items` SET `company_id` = 1 WHERE `company_id` IS NULL LIMIT 1000;
UPDATE `vendors` SET `company_id` = 1 WHERE `company_id` IS NULL LIMIT 1000;

-- Enterprise production (payments + security)
CREATE TABLE IF NOT EXISTS `invoice_payments` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` enum('cash','bank','card','cheque','online') NOT NULL DEFAULT 'bank',
  `reference_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `paid_at` datetime NOT NULL,
  `recorded_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_inv_pay_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` varchar(120) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_login_email_time` (`email`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `invoices` MODIFY `status` enum('draft','sent','partial','paid','overdue','cancelled') NOT NULL DEFAULT 'draft';
