-- v1.22: Suppliers module + link items to default supplier
-- Run once on your DB.

CREATE TABLE IF NOT EXISTS suppliers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(40) NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  phone2 VARCHAR(40) NULL,
  email VARCHAR(160) NULL,
  address VARCHAR(255) NULL,
  tax_no VARCHAR(80) NULL,
  notes VARCHAR(255) NULL,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_suppliers_code (code),
  KEY idx_suppliers_name (name),
  KEY idx_suppliers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Link items to one default supplier (optional)
ALTER TABLE items
  ADD COLUMN default_supplier_id BIGINT UNSIGNED NULL AFTER brand;

ALTER TABLE items
  ADD KEY idx_items_default_supplier (default_supplier_id);

ALTER TABLE items
  ADD CONSTRAINT fk_items_default_supplier
    FOREIGN KEY (default_supplier_id) REFERENCES suppliers(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

-- Permissions
INSERT IGNORE INTO permissions (code, name, module) VALUES
  ('suppliers.view','عرض الموردين','purchases'),
  ('suppliers.edit','إضافة/تعديل الموردين','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 ('suppliers.view','suppliers.edit');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, p.id FROM permissions p WHERE p.code IN ('suppliers.view','suppliers.edit');