USE pos_system;

ALTER TABLE sales
  MODIFY status ENUM('completed','void_requested','voided') NOT NULL DEFAULT 'completed';

ALTER TABLE sales
  ADD COLUMN IF NOT EXISTS void_reason VARCHAR(255) NULL AFTER status,
  ADD COLUMN IF NOT EXISTS voided_by INT NULL AFTER void_reason,
  ADD COLUMN IF NOT EXISTS voided_at TIMESTAMP NULL DEFAULT NULL AFTER voided_by;

-- FK consistency note:
-- final_schema.sql defines sales.voided_by -> users.id with ON DELETE SET NULL.
-- MySQL/MariaDB do not support a portable ADD CONSTRAINT IF NOT EXISTS syntax.
-- If this migration was already applied before the FK existed, verify the constraint first:
-- SELECT CONSTRAINT_NAME
-- FROM information_schema.KEY_COLUMN_USAGE
-- WHERE TABLE_SCHEMA = DATABASE()
--   AND TABLE_NAME = 'sales'
--   AND COLUMN_NAME = 'voided_by'
--   AND REFERENCED_TABLE_NAME = 'users';
-- Then, only if no matching constraint exists, run:
-- ALTER TABLE sales
--   ADD CONSTRAINT fk_sales_voided_by
--   FOREIGN KEY (voided_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE daily_closings
  ADD COLUMN IF NOT EXISTS void_count INT NOT NULL DEFAULT 0 AFTER return_count,
  ADD COLUMN IF NOT EXISTS void_total DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER void_count;

INSERT IGNORE INTO role_permissions(role_name, permission_key, is_allowed) VALUES
('Area Manager', 'sales.void.request', 1),
('Area Manager', 'sales.void.approve', 1),
('Manager', 'sales.void.request', 1),
('Manager', 'sales.void.approve', 1),
('Cashier', 'sales.void.request', 1);
