-- Sprint 8 Patch 9 - Invoice Template + Settings

CREATE TABLE IF NOT EXISTS invoice_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(60) NOT NULL,
  name VARCHAR(120) NOT NULL,
  template_type VARCHAR(30) NOT NULL DEFAULT 'a4',
  description VARCHAR(255) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  config_json JSON DEFAULT NULL,
  created_at DATETIME DEFAULT NULL,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invoice_templates_code (code),
  KEY idx_invoice_templates_type (template_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO invoice_templates (code, name, template_type, description, sort_order, is_active, created_at, updated_at)
SELECT 'standard', 'Standar Polos', 'a4', 'Clean formal untuk quotation dan invoice.', 10, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM invoice_templates WHERE code = 'standard');

INSERT INTO invoice_templates (code, name, template_type, description, sort_order, is_active, created_at, updated_at)
SELECT 'modern_1', 'Modern 1', 'a4', 'Header branding dengan warna utama.', 20, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM invoice_templates WHERE code = 'modern_1');

INSERT INTO invoice_templates (code, name, template_type, description, sort_order, is_active, created_at, updated_at)
SELECT 'modern_2', 'Modern 2', 'a4', 'Layout sidebar untuk branding yang lebih tegas.', 30, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM invoice_templates WHERE code = 'modern_2');

CREATE TABLE IF NOT EXISTS invoice_template_active (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  active_a4_template_id BIGINT UNSIGNED NOT NULL,
  primary_color VARCHAR(7) DEFAULT '#2563eb',
  company_name VARCHAR(180) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  whatsapp VARCHAR(60) DEFAULT NULL,
  email VARCHAR(120) DEFAULT NULL,
  website VARCHAR(120) DEFAULT NULL,
  bank_accounts_json JSON DEFAULT NULL,
  quotation_notes TEXT DEFAULT NULL,
  invoice_notes TEXT DEFAULT NULL,
  digital_stamp_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT NULL,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invoice_template_active_loc (tenant_id, location_id),
  KEY idx_invoice_template_active_tpl (active_a4_template_id),
  CONSTRAINT fk_invoice_template_active_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_invoice_template_active_location FOREIGN KEY (location_id) REFERENCES locations (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_invoice_template_active_tpl FOREIGN KEY (active_a4_template_id) REFERENCES invoice_templates (id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO invoice_template_active (
  tenant_id, location_id, active_a4_template_id, primary_color, company_name, quotation_notes, invoice_notes, digital_stamp_enabled, created_at, updated_at
)
SELECT
  l.tenant_id,
  l.id,
  (SELECT id FROM invoice_templates WHERE code = 'standard' LIMIT 1),
  '#2563eb',
  l.name,
  'Penawaran berlaku 7 hari sejak tanggal terbit.\nHarga dapat berubah jika ada revisi spesifikasi.',
  'Terima kasih atas kepercayaan Anda.\nMohon konfirmasi setelah pembayaran dilakukan.',
  1,
  NOW(),
  NOW()
FROM locations l
WHERE NOT EXISTS (
  SELECT 1 FROM invoice_template_active ita WHERE ita.tenant_id = l.tenant_id AND ita.location_id = l.id
);

CREATE TABLE IF NOT EXISTS delivery_notes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  shipment_id BIGINT UNSIGNED DEFAULT NULL,
  note_no VARCHAR(80) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  created_at DATETIME DEFAULT NULL,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_delivery_notes_no (note_no),
  UNIQUE KEY uq_delivery_notes_order (tenant_id, location_id, order_id),
  KEY idx_delivery_notes_shipment (shipment_id),
  CONSTRAINT fk_delivery_notes_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_delivery_notes_location FOREIGN KEY (location_id) REFERENCES locations (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_delivery_notes_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_delivery_notes_shipment FOREIGN KEY (shipment_id) REFERENCES shipments (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO permissions (code, name, module, created_at)
SELECT 'settings.invoice.view', 'Lihat Pengaturan Invoice', 'SETTINGS', NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'settings.invoice.view');

INSERT INTO permissions (code, name, module, created_at)
SELECT 'settings.invoice.edit', 'Edit Pengaturan Invoice', 'SETTINGS', NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'settings.invoice.edit');

INSERT INTO role_permissions (role_id, permission_id)
SELECT rp.role_id, p.id
FROM (
  SELECT 1 AS role_id UNION ALL SELECT 2 UNION ALL SELECT 3
) rp
JOIN permissions p ON p.code = 'settings.invoice.view'
WHERE NOT EXISTS (
  SELECT 1 FROM role_permissions x WHERE x.role_id = rp.role_id AND x.permission_id = p.id
);

INSERT INTO role_permissions (role_id, permission_id)
SELECT rp.role_id, p.id
FROM (
  SELECT 1 AS role_id UNION ALL SELECT 2 UNION ALL SELECT 3
) rp
JOIN permissions p ON p.code = 'settings.invoice.edit'
WHERE NOT EXISTS (
  SELECT 1 FROM role_permissions x WHERE x.role_id = rp.role_id AND x.permission_id = p.id
);