-- Phase 13: Expenses Module Phase 1

CREATE TABLE IF NOT EXISTS expense_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_expense_categories_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_expense_items_category FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE KEY uniq_expense_items_cat_name (category_id, name),
  KEY idx_expense_items_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO permissions (code, name, module) VALUES
('expenses.categories.view', 'عرض مجموعات المصروفات', 'expenses'),
('expenses.categories.create', 'إضافة/تعديل مجموعات المصروفات', 'expenses'),
('expenses.items.view', 'عرض بنود المصروفات', 'expenses'),
('expenses.items.create', 'إضافة/تعديل بنود المصروفات', 'expenses'),
('expenses.report.view', 'عرض تقارير المصروفات', 'expenses');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, p.id FROM permissions p
WHERE p.code IN (
  'expenses.categories.view',
  'expenses.categories.create',
  'expenses.items.view',
  'expenses.items.create',
  'expenses.report.view'
);