-- =============================================================================
-- Account-wise approval workflow (greenfield)
-- Replaces project_incharges / project_approval_levels with per-project accounts.
-- Run against: dyu_vendor_payment_db (or your app DB)
-- WARNING: Truncates payment request / approval runtime data that depended on
--          the old project-level hierarchy.
-- =============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Runtime tables that FK to old project_approval_levels
DROP TABLE IF EXISTS payment_request_approval_attachments;
DROP TABLE IF EXISTS payment_request_approval_logs;
DROP TABLE IF EXISTS payment_request_approvals;

-- Old project-level config
DROP TABLE IF EXISTS project_approval_levels;
DROP TABLE IF EXISTS project_incharges;

-- Clear letter headers so account_id can be NOT NULL
DELETE FROM payment_request_status_logs;
DELETE FROM payment_request_attachments;
DELETE FROM payment_transactions;
DELETE FROM payment_requests;

-- -----------------------------------------------------------------------------
-- Project accounts (departments / sections)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  account_code VARCHAR(50) NOT NULL,
  account_name VARCHAR(200) NOT NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_project_account_code (project_id, account_code),
  CONSTRAINT fk_project_accounts_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_project_accounts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_project_accounts_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_project_accounts_project (project_id),
  INDEX idx_project_accounts_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Accounts (departments) under a project; each has its own approval chain.';

CREATE TABLE IF NOT EXISTS project_account_incharges (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  assigned_from DATE NULL,
  assigned_to DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  assigned_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_account_incharge (account_id, user_id),
  CONSTRAINT fk_account_incharges_account FOREIGN KEY (account_id) REFERENCES project_accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_incharges_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_incharges_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_account_incharges_user (user_id),
  INDEX idx_account_incharges_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Users who may raise payment letters for an account.';

CREATE TABLE IF NOT EXISTS project_account_approval_levels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  level_name VARCHAR(100) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_account_level (account_id, level_number),
  CONSTRAINT fk_account_levels_account FOREIGN KEY (account_id) REFERENCES project_accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_levels_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_account_levels_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_account_levels_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Ordered approval levels for an account; approvers live in child table.';

CREATE TABLE IF NOT EXISTS project_account_approval_users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  approval_level_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_account_level_user (approval_level_id, user_id),
  CONSTRAINT fk_account_level_users_level FOREIGN KEY (approval_level_id) REFERENCES project_account_approval_levels(id) ON DELETE CASCADE,
  CONSTRAINT fk_account_level_users_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_account_level_users_user (user_id),
  INDEX idx_account_level_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Approvers mapped to an account approval level (any one may complete the level).';

-- -----------------------------------------------------------------------------
-- payment_requests.account_id
-- -----------------------------------------------------------------------------
ALTER TABLE payment_requests
  ADD COLUMN account_id BIGINT UNSIGNED NOT NULL AFTER project_id,
  ADD CONSTRAINT fk_payment_requests_account FOREIGN KEY (account_id) REFERENCES project_accounts(id) ON DELETE RESTRICT,
  ADD INDEX idx_payment_requests_account (account_id),
  ADD INDEX idx_payment_requests_project_account (project_id, account_id);

-- -----------------------------------------------------------------------------
-- Recreate runtime approvals (multi-assignee per level)
-- -----------------------------------------------------------------------------
CREATE TABLE payment_request_approvals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  account_approval_level_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('PENDING','APPROVED','REJECTED','SKIPPED','RETURNED_FOR_AMENDMENT') NOT NULL DEFAULT 'PENDING',
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  acted_at DATETIME NULL,
  remarks TEXT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_request_approvals_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_request_approvals_level FOREIGN KEY (account_approval_level_id) REFERENCES project_account_approval_levels(id) ON DELETE RESTRICT,
  CONSTRAINT fk_request_approvals_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  UNIQUE KEY uq_request_level_approver (payment_request_id, level_number, approver_user_id),
  INDEX idx_request_approvals_user_status (approver_user_id, status),
  INDEX idx_request_approvals_current (is_current),
  INDEX idx_request_approvals_level (payment_request_id, level_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='One row per assignee per level; any one APPROVED completes the level (peers SKIPPED).';

CREATE TABLE payment_request_approval_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  approval_id BIGINT UNSIGNED NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  action ENUM('ASSIGNED','APPROVED','REJECTED','RETURNED_FOR_AMENDMENT','RESUBMITTED','FORWARDED','AUTO_SKIPPED') NOT NULL,
  remarks TEXT NULL,
  previous_level INT UNSIGNED NULL,
  next_level INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_approval_logs_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_logs_approval FOREIGN KEY (approval_id) REFERENCES payment_request_approvals(id) ON DELETE SET NULL,
  CONSTRAINT fk_approval_logs_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_approval_logs_request (payment_request_id),
  INDEX idx_approval_logs_user (approver_user_id),
  INDEX idx_approval_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Immutable approval action trail with remarks and level movement.';

CREATE TABLE payment_request_approval_attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  approval_id BIGINT UNSIGNED NOT NULL,
  approval_log_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  action ENUM('APPROVED','REJECTED','RETURNED_FOR_AMENDMENT') NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  original_file_name VARCHAR(255) NULL,
  file_path VARCHAR(600) NOT NULL,
  file_url VARCHAR(800) NULL,
  mime_type VARCHAR(100) NULL,
  file_size_bytes BIGINT UNSIGNED NULL,
  remarks TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_approval_attach_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_approval FOREIGN KEY (approval_id) REFERENCES payment_request_approvals(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_log FOREIGN KEY (approval_log_id) REFERENCES payment_request_approval_logs(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_approval_attach_request (payment_request_id),
  INDEX idx_approval_attach_approval (approval_id),
  INDEX idx_approval_attach_log (approval_log_id),
  INDEX idx_approval_attach_level (payment_request_id, level_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Optional files uploaded by approvers when acting on a level.';

SET FOREIGN_KEY_CHECKS = 1;