-- v1.29: Supplier Payments module
-- Run once on your DB.

CREATE TABLE IF NOT EXISTS supplier_payments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  pay_no VARCHAR(60) NULL,
  supplier_id BIGINT UNSIGNED NOT NULL,
  pay_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  method VARCHAR(20) NOT NULL DEFAULT 'CASH', -- CASH / TRANSFER / CHEQUE
  ref_no VARCHAR(80) NULL,
  notes VARCHAR(255) NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'DRAFT', -- DRAFT / POSTED
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  posted_by BIGINT UNSIGNED NULL,
  posted_at TIMESTAMP NULL,
  KEY idx_sp_supplier_date (supplier_id, pay_date),
  KEY idx_sp_status (status),
  CONSTRAINT fk_sp_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_sp_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_sp_posted_by FOREIGN KEY (posted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Permissions
INSERT IGNORE INTO permissions (code, name, module) VALUES
  ('supplier_payments.view', 'عرض مدفوعات الموردين', 'purchases'),
  ('supplier_payments.create', 'إنشاء مدفوعات الموردين', 'purchases'),
  ('supplier_payments.edit', 'تعديل مدفوعات الموردين', 'purchases'),
  ('supplier_payments.post', 'ترحيل مدفوعات الموردين', 'purchases'),
  ('supplier_payments.print', 'طباعة مدفوعات الموردين', 'purchases');

-- Grant to Admin and Manager
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, p.id FROM permissions p WHERE p.code IN (
  'supplier_payments.view','supplier_payments.create','supplier_payments.edit','supplier_payments.post','supplier_payments.print'
);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, p.id FROM permissions p WHERE p.code IN (
  'supplier_payments.view','supplier_payments.create','supplier_payments.edit','supplier_payments.post','supplier_payments.print'
);