-- Migration: Create ptkpstatuses table for Indonesian tax status (PTKP)
-- Values: TK/0, TK/1, TK/2, TK/3, K/0, K/1, K/2, K/3, K/I/0, K/I/1, K/I/2, K/I/3, M/0, M/1, M/2, M/3, S/0
-- Safe migration: will not run if table already exists, seed only if table is empty

-- Step 1: Create the table if it doesn't exist
CREATE TABLE IF NOT EXISTS `ptkpstatuses` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` INT(11) NOT NULL DEFAULT 1,
  `owner` INT(11) NOT NULL DEFAULT 1,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `name` VARCHAR(50) NOT NULL,
  `display_name` VARCHAR(50) NOT NULL,
  `company_id` INT(11) DEFAULT NULL,
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_ptkpstatuses_company_id` (`company_id`),
  INDEX `idx_ptkpstatuses_display_name` (`display_name`),
  INDEX `idx_ptkpstatuses_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 2: Seed common PTKP status values (only if not already exists)
-- Uses valid user ID from users table to satisfy FK constraint on modified_by/owner

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'tk-0', 'TK/0', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'tk-0' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'tk-1', 'TK/1', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'tk-1' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'tk-2', 'TK/2', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'tk-2' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'tk-3', 'TK/3', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'tk-3' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-0', 'K/0', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-0' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-1', 'K/1', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-1' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-2', 'K/2', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-2' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-3', 'K/3', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-3' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-i-0', 'K/I/0', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-i-0' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-i-1', 'K/I/1', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-i-1' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-i-2', 'K/I/2', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-i-2' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'k-i-3', 'K/I/3', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'k-i-3' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'm-0', 'M/0', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'm-0' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'm-1', 'M/1', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'm-1' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'm-2', 'M/2', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'm-2' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 'm-3', 'M/3', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 'm-3' LIMIT 1);

INSERT INTO `ptkpstatuses` (`name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
SELECT 's-0', 'S/0', NOW(), NOW(), u.id, u.id, 1 FROM (SELECT MIN(id) AS id FROM `users`) u
WHERE NOT EXISTS (SELECT 1 FROM `ptkpstatuses` WHERE `name` = 's-0' LIMIT 1);