-- v1.23: Purchases (Purchase Invoices, Returns, Supplier Ledger)
-- Run once after previous migrations.

SET NAMES utf8mb4;

-- Purchase invoices
CREATE TABLE IF NOT EXISTS purchase_invoices (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  inv_no VARCHAR(40) NOT NULL UNIQUE,
  supplier_id BIGINT UNSIGNED NOT NULL,
  warehouse_id BIGINT UNSIGNED NOT NULL,
  inv_date DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'DRAFT', -- DRAFT/POSTED/CANCELED
  notes VARCHAR(255) NULL,

  discount_type VARCHAR(10) NOT NULL DEFAULT 'PERCENT', -- PERCENT/AMOUNT
  discount_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount_note VARCHAR(255) NULL,

  tax_enabled TINYINT(1) NOT NULL DEFAULT 0,
  tax_rate DECIMAL(6,2) NOT NULL DEFAULT 0,
  tax_invoice_no VARCHAR(80) NULL,

  extra_charges DECIMAL(14,2) NOT NULL DEFAULT 0,
  charges_note VARCHAR(255) NULL,

  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,

  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  posted_by BIGINT UNSIGNED NULL,
  posted_at DATETIME NULL,

  KEY idx_pi_supplier_date (supplier_id, inv_date),
  KEY idx_pi_wh_date (warehouse_id, inv_date),
  CONSTRAINT fk_pi_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_pi_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_pi_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_pi_posted_by FOREIGN KEY (posted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_invoice_lines (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,

  KEY idx_pil_invoice (invoice_id),
  CONSTRAINT fk_pil_invoice FOREIGN KEY (invoice_id) REFERENCES purchase_invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_pil_item FOREIGN KEY (item_id) REFERENCES items(id),
  CONSTRAINT fk_pil_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Purchase returns (linked to invoice)
CREATE TABLE IF NOT EXISTS purchase_returns (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  ret_no VARCHAR(40) NOT NULL UNIQUE,
  supplier_id BIGINT UNSIGNED NOT NULL,
  warehouse_id BIGINT UNSIGNED NOT NULL,
  ret_date DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'DRAFT', -- DRAFT/POSTED/CANCELED
  source_invoice_id BIGINT UNSIGNED NOT NULL,
  notes VARCHAR(255) NULL,

  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,

  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  posted_by BIGINT UNSIGNED NULL,
  posted_at DATETIME NULL,

  KEY idx_pr_supplier_date (supplier_id, ret_date),
  KEY idx_pr_source (source_invoice_id),
  CONSTRAINT fk_pr_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_pr_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_pr_source FOREIGN KEY (source_invoice_id) REFERENCES purchase_invoices(id),
  CONSTRAINT fk_pr_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_pr_posted_by FOREIGN KEY (posted_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_return_lines (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  return_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,

  KEY idx_prl_return (return_id),
  CONSTRAINT fk_prl_return FOREIGN KEY (return_id) REFERENCES purchase_returns(id) ON DELETE CASCADE,
  CONSTRAINT fk_prl_item FOREIGN KEY (item_id) REFERENCES items(id),
  CONSTRAINT fk_prl_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Supplier ledger for statements
CREATE TABLE IF NOT EXISTS supplier_ledger (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  supplier_id BIGINT UNSIGNED NOT NULL,
  doc_type VARCHAR(30) NOT NULL, -- PURCHASE_INV / PURCHASE_RET / PAYMENT / OPENING
  doc_id BIGINT UNSIGNED NOT NULL,
  doc_no VARCHAR(60) NULL,
  doc_date DATETIME NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sl_supplier_date (supplier_id, doc_date),
  KEY idx_sl_doc (doc_type, doc_id),
  CONSTRAINT fk_sl_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_sl_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Permissions
INSERT IGNORE INTO permissions (code, name, module) VALUES
  ('purchases.view', 'عرض فواتير الشراء', 'purchases'),
  ('purchases.create', 'إنشاء فاتورة شراء', 'purchases'),
  ('purchases.edit', 'تعديل فاتورة شراء', 'purchases'),
  ('purchases.post', 'ترحيل فاتورة شراء', 'purchases'),
  ('purchases.print', 'طباعة فاتورة شراء', 'purchases'),
  ('purchase_returns.view', 'عرض مرتجعات الشراء', 'purchases'),
  ('purchase_returns.create', 'إنشاء مرتجع شراء', 'purchases'),
  ('purchase_returns.post', 'ترحيل مرتجع شراء', 'purchases'),
  ('purchase_returns.print', 'طباعة مرتجع شراء', 'purchases'),
  ('suppliers.statement.view', 'كشف حساب المورد', 'purchases'),
  ('suppliers.statement.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 (
  'purchases.view','purchases.create','purchases.edit','purchases.post','purchases.print',
  'purchase_returns.view','purchase_returns.create','purchase_returns.post','purchase_returns.print',
  'suppliers.statement.view','suppliers.statement.print'
);

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