-- Shift / cash register module (run on existing POS26 database)
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS register_shifts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  terminal_name VARCHAR(80) NULL,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at DATETIME NULL,
  opening_note VARCHAR(500) NULL,
  closing_note VARCHAR(500) NULL,
  opened_by INT UNSIGNED NOT NULL,
  closed_by INT UNSIGNED NULL,
  KEY idx_rs_branch_status (branch_id, status),
  KEY idx_rs_user_status (user_id, status),
  KEY idx_rs_opened (opened_at),
  CONSTRAINT fk_rs_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_rs_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_rs_opened_by FOREIGN KEY (opened_by) REFERENCES users(id),
  CONSTRAINT fk_rs_closed_by FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS register_shift_balances (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  shift_id INT UNSIGNED NOT NULL,
  payment_method_id INT UNSIGNED NOT NULL,
  opening_amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  closing_amount DECIMAL(18,4) NULL,
  expected_amount DECIMAL(18,4) NULL,
  sales_amount DECIMAL(18,4) NULL,
  handover_in DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  handover_out DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  UNIQUE KEY uq_rsb_shift_pm (shift_id, payment_method_id),
  CONSTRAINT fk_rsb_shift FOREIGN KEY (shift_id) REFERENCES register_shifts(id) ON DELETE CASCADE,
  CONSTRAINT fk_rsb_pm FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS register_shift_handovers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  from_shift_id INT UNSIGNED NULL,
  to_shift_id INT UNSIGNED NULL,
  from_user_id INT UNSIGNED NOT NULL,
  to_user_id INT UNSIGNED NOT NULL,
  payment_method_id INT UNSIGNED NOT NULL,
  amount DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  status ENUM('pending','accepted','cancelled') NOT NULL DEFAULT 'pending',
  note VARCHAR(500) NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accepted_at DATETIME NULL,
  accepted_by INT UNSIGNED NULL,
  KEY idx_rsh_to_user (to_user_id, status),
  KEY idx_rsh_branch (branch_id, created_at),
  CONSTRAINT fk_rsh_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
  CONSTRAINT fk_rsh_from_shift FOREIGN KEY (from_shift_id) REFERENCES register_shifts(id) ON DELETE SET NULL,
  CONSTRAINT fk_rsh_to_shift FOREIGN KEY (to_shift_id) REFERENCES register_shifts(id) ON DELETE SET NULL,
  CONSTRAINT fk_rsh_from_user FOREIGN KEY (from_user_id) REFERENCES users(id),
  CONSTRAINT fk_rsh_to_user FOREIGN KEY (to_user_id) REFERENCES users(id),
  CONSTRAINT fk_rsh_pm FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id),
  CONSTRAINT fk_rsh_created FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_rsh_accepted FOREIGN KEY (accepted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sales.shift_id (ignore error if column exists)
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales' AND COLUMN_NAME = 'shift_id');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE sales ADD COLUMN shift_id INT UNSIGNED NULL AFTER created_by, ADD KEY idx_sale_shift (shift_id)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO permissions (module, action, slug) VALUES
('shifts', 'view', 'shifts.view'),
('shifts', 'open', 'shifts.open'),
('shifts', 'close', 'shifts.close'),
('shifts', 'handover', 'shifts.handover'),
('shifts', 'report', 'shifts.report');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE slug LIKE 'shifts.%';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE slug LIKE 'shifts.%';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE slug IN ('shifts.view','shifts.open','shifts.close','shifts.handover');

INSERT IGNORE INTO settings (`key`, `value`) VALUES ('shift_required', '1');