-- ============================================================
-- Green House / البيت الأخضر — ملف SQL الوحيد
-- Database: uznet_invoices
-- نفّذه مرة واحدة في phpMyAdmin
-- بدون FOREIGN KEY صارمة لتجنب errno 150 على cPanel/MariaDB
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS contracts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  total_amount BIGINT NOT NULL DEFAULT 0,
  contract_date DATE NULL,
  phone VARCHAR(50) NULL,
  units_count DECIMAL(15,3) NOT NULL DEFAULT 1.000,
  unit_price BIGINT NOT NULL DEFAULT 0,
  invoice_language ENUM('ar','en') NOT NULL DEFAULT 'en',
  include_stamp TINYINT(1) NOT NULL DEFAULT 1,
  include_signature TINYINT(1) NOT NULL DEFAULT 1,
  send_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
  send_time TIME NOT NULL DEFAULT '09:00:00',
  next_send_at DATETIME NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_contracts_active (is_active),
  INDEX idx_contracts_next_send (next_send_at, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contract_emails (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_id INT UNSIGNED NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_contract_email (contract_id, email),
  INDEX idx_emails_contract (contract_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_accounts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  display_name VARCHAR(255) NOT NULL DEFAULT 'Green House Company',
  smtp_host VARCHAR(255) NULL,
  smtp_port INT UNSIGNED NOT NULL DEFAULT 465,
  smtp_user VARCHAR(255) NULL,
  smtp_pass VARCHAR(255) NULL,
  smtp_secure ENUM('ssl','tls','') NOT NULL DEFAULT 'ssl',
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mail_accounts_email (email),
  INDEX idx_mail_accounts_order (is_active, sort_order, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contract_mail_accounts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_id INT UNSIGNED NOT NULL,
  mail_account_id INT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_contract_mail (contract_id, mail_account_id),
  INDEX idx_cma_contract_order (contract_id, sort_order, id),
  INDEX idx_cma_mail (mail_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contract_sites (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contract_id INT UNSIGNED NOT NULL,
  site_name VARCHAR(255) NOT NULL,
  service_type VARCHAR(120) NULL COMMENT 'نوع الخدمة',
  site_amount BIGINT NOT NULL DEFAULT 0,
  units_count DECIMAL(15,3) NOT NULL DEFAULT 1.000,
  units_label VARCHAR(120) NULL COMMENT 'عرض الكمية كما في الفاتورة: رقم أو عبارة',
  unit_price BIGINT NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sites_contract (contract_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(50) NOT NULL,
  contract_id INT UNSIGNED NULL,
  customer_name VARCHAR(255) NOT NULL,
  service_type VARCHAR(120) NULL COMMENT 'نوع الخدمة',
  invoice_date DATE NOT NULL,
  total_amount BIGINT NOT NULL DEFAULT 0,
  amount_in_words VARCHAR(500) NULL,
  language ENUM('ar','en') NOT NULL DEFAULT 'en',
  include_stamp TINYINT(1) NOT NULL DEFAULT 1,
  include_signature TINYINT(1) NOT NULL DEFAULT 1,
  pdf_path VARCHAR(500) NULL,
  status ENUM('draft','generated','sent','failed') NOT NULL DEFAULT 'draft',
  sent_at DATETIME NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_invoice_customer_number (contract_id, invoice_number),
  INDEX idx_invoices_contract (contract_id),
  INDEX idx_invoices_status (status),
  INDEX idx_invoices_date (invoice_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_lines (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  service_type VARCHAR(120) NULL COMMENT 'نوع الخدمة',
  units_count DECIMAL(15,3) NOT NULL DEFAULT 1.000 COMMENT 'قيمة رقمية للحساب إن لزم',
  units_label VARCHAR(120) NULL COMMENT 'عرض الكمية كما في الفاتورة: رقم أو عبارة مثل شهر واحد',
  unit_price BIGINT NOT NULL DEFAULT 0,
  line_total BIGINT NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  INDEX idx_lines_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_email_log (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT UNSIGNED NOT NULL,
  email VARCHAR(255) NOT NULL,
  status ENUM('sent','failed') NOT NULL,
  error_message TEXT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_log_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_key, setting_value) VALUES
  ('company_name_ar', 'شركة البيت الأخضر'),
  ('company_name_en', 'Green House Company'),
  ('finance_label_ar', 'الإدارة المالية'),
  ('finance_label_en', 'Financial Management'),
  ('invoice_prefix', ''),
  ('currency_ar', 'دينار'),
  ('currency_en', 'IQD'),
  ('api_token', 'gh-invoices-secret-change-me-2026')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- ترقية قواعد قديمة — نفّذها قبل إدراج العميل 1 إن الجداول موجودة مسبقاً
ALTER TABLE contract_sites
  ADD COLUMN IF NOT EXISTS service_type VARCHAR(120) NULL COMMENT 'نوع الخدمة' AFTER site_name;
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS service_type VARCHAR(120) NULL COMMENT 'نوع الخدمة' AFTER customer_name;
ALTER TABLE invoice_lines
  ADD COLUMN IF NOT EXISTS service_type VARCHAR(120) NULL COMMENT 'نوع الخدمة' AFTER description;
ALTER TABLE invoice_lines
  ADD COLUMN IF NOT EXISTS units_label VARCHAR(120) NULL COMMENT 'عرض الكمية كما في الفاتورة' AFTER units_count;
ALTER TABLE contract_sites
  ADD COLUMN IF NOT EXISTS units_label VARCHAR(120) NULL COMMENT 'عرض الكمية كما في الفاتورة' AFTER units_count;

-- دينار عراقي صحيح بلا فلوس. يحوّل الأعمدة إن كانت DECIMAL من قاعدة قديمة
ALTER TABLE contracts
  MODIFY total_amount BIGINT NOT NULL DEFAULT 0,
  MODIFY unit_price BIGINT NOT NULL DEFAULT 0;
ALTER TABLE contract_sites
  MODIFY site_amount BIGINT NOT NULL DEFAULT 0,
  MODIFY unit_price BIGINT NOT NULL DEFAULT 0;
ALTER TABLE invoices
  MODIFY total_amount BIGINT NOT NULL DEFAULT 0;
ALTER TABLE invoice_lines
  MODIFY unit_price BIGINT NOT NULL DEFAULT 0,
  MODIFY line_total BIGINT NOT NULL DEFAULT 0;

-- رقم الفاتورة خاص بكل زبون: زبونان يمكن أن يكون لكل منهما فاتورة رقم 1
ALTER TABLE invoices DROP INDEX IF EXISTS uq_invoice_number;
CREATE UNIQUE INDEX IF NOT EXISTS uq_invoice_customer_number ON invoices (contract_id, invoice_number);

-- ---------- بيانات أولية اختيارية: العميل 1 (Trade Bank of Iraq) ----------
INSERT INTO mail_accounts (email, display_name, sort_order, is_active)
SELECT 'info@ghservices.me', 'Green House Company', 0, 1
WHERE NOT EXISTS (
  SELECT 1 FROM mail_accounts WHERE email = 'info@ghservices.me'
);

INSERT INTO contracts (
  name, total_amount, contract_date, phone,
  units_count, unit_price, invoice_language,
  include_stamp, include_signature,
  send_day, send_time, next_send_at, is_active, notes
)
SELECT
  'Trade Bank of Iraq',
  5800000,
  '2025-05-04',
  NULL,
  1.000,
  5800000,
  'en',
  1,
  1,
  4,
  '09:00:00',
  CONCAT(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-04'), ' 09:00:00'),
  1,
  'Imported from historical invoice #1 | Work 2025-04-04 to 2025-05-04 | Cleaning the bank building'
WHERE NOT EXISTS (
  SELECT 1 FROM contracts WHERE name = 'Trade Bank of Iraq'
);

SET @contract_id = (
  SELECT id FROM contracts WHERE name = 'Trade Bank of Iraq' ORDER BY id DESC LIMIT 1
);

INSERT INTO contract_emails (contract_id, email)
SELECT @contract_id, 'financial@ghservices.me'
WHERE @contract_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM contract_emails
    WHERE contract_id = @contract_id AND email = 'financial@ghservices.me'
  );

INSERT INTO contract_mail_accounts (contract_id, mail_account_id, sort_order)
SELECT @contract_id, ma.id, 0
FROM mail_accounts ma
WHERE ma.email = 'info@ghservices.me'
  AND @contract_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM contract_mail_accounts
    WHERE contract_id = @contract_id AND mail_account_id = ma.id
  )
LIMIT 1;

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at
)
SELECT
  '1',
  @contract_id,
  'Trade Bank of Iraq',
  'Cleaning',
  '2025-05-04',
  5800000,
  'Only five million eight hundred thousand Iraqi dinars, nothing else',
  'en',
  1,
  1,
  'sent',
  '2025-05-04 09:00:00'
WHERE @contract_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM invoices WHERE invoice_number = '1'
  );

SET @invoice_id = (
  SELECT id FROM invoices WHERE invoice_number = '1' LIMIT 1
);

INSERT INTO invoice_lines (
  invoice_id, description, service_type, units_count, unit_price, line_total, sort_order
)
SELECT
  @invoice_id,
  'Cleaning the bank building',
  'Cleaning',
  1.000,
  5800000,
  5800000,
  0
WHERE @invoice_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM invoice_lines WHERE invoice_id = @invoice_id
  );

-- ---------- العميل 2: الشركة الوطنية المتحدة مول الجادرية — 39 فاتورة أرشيف ----------
INSERT INTO contracts (
  name, total_amount, contract_date, phone,
  units_count, unit_price, invoice_language,
  include_stamp, include_signature,
  send_day, send_time, next_send_at, is_active, notes
)
SELECT
  'الشركة الوطنية المتحدة مول الجادرية',
  1000000,
  '2023-07-01',
  NULL, 1.000,
  1000000,
  'en', 1, 1,
  1, '09:00:00',
  CONCAT(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'), ' 09:00:00'),
  1,
  'Client 2 — مكافحة حشرات مول الجادرية | 39 فواتير أرشيف'
WHERE NOT EXISTS (
  SELECT 1 FROM contracts WHERE name = 'الشركة الوطنية المتحدة مول الجادرية'
);

SET @c2 = (SELECT id FROM contracts WHERE name = 'الشركة الوطنية المتحدة مول الجادرية' ORDER BY id DESC LIMIT 1);

INSERT INTO contract_emails (contract_id, email)
SELECT @c2, 'financial@ghservices.me'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM contract_emails WHERE contract_id = @c2 AND email = 'financial@ghservices.me'
);

INSERT INTO contract_mail_accounts (contract_id, mail_account_id, sort_order)
SELECT @c2, ma.id, 0 FROM mail_accounts ma
WHERE ma.email = 'info@ghservices.me' AND @c2 IS NOT NULL
AND NOT EXISTS (
  SELECT 1 FROM contract_mail_accounts WHERE contract_id = @c2 AND mail_account_id = ma.id
)
LIMIT 1;

INSERT INTO contract_sites (contract_id, site_name, service_type, site_amount, units_count, units_label, unit_price, sort_order)
SELECT @c2,
  'Pest control at Al-Jadriya Mall', 'Pest control',
  1000000, 1.000, 'One month', 1000000, 0
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM contract_sites WHERE contract_id = @c2
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '2', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2023-08-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2023-08-01', ' 09:00:00'), 'PDF 2.pdf legacy INVOICE #2'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '2'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '2' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '3', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2023-08-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2023-08-01', ' 09:00:00'), 'PDF 3.pdf legacy INVOICE #30'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '3'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '3' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '4', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2023-09-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2023-09-01', ' 09:00:00'), 'PDF 4.pdf legacy INVOICE #4'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '4'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '4' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '5', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2023-10-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2023-10-01', ' 09:00:00'), 'PDF 5.pdf legacy INVOICE #5'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '5'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '5' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '6', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2023-11-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2023-11-01', ' 09:00:00'), 'PDF 6.pdf legacy INVOICE #60'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '6'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '6' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '7', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2023-02-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2023-02-01', ' 09:00:00'), 'PDF 7.pdf legacy INVOICE #7'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '7'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '7' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '8', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-01-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-01-01', ' 09:00:00'), 'PDF 8.pdf legacy INVOICE #80'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '8'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '8' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '9', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-02-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-02-01', ' 09:00:00'), 'PDF 9.pdf legacy INVOICE #90'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '9'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '9' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '10', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-02-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-02-01', ' 09:00:00'), 'PDF 10.pdf legacy INVOICE #10'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '10'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '10' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '11', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-04-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-04-01', ' 09:00:00'), 'PDF 11.pdf legacy INVOICE #11'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '11'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '11' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '12', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-05-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-05-01', ' 09:00:00'), 'PDF 12.pdf legacy INVOICE #12'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '12'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '12' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '13', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-06-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-06-01', ' 09:00:00'), 'PDF 13.pdf legacy INVOICE #31'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '13'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '13' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '14', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-06-01',
  500, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-06-01', ' 09:00:00'), 'PDF 14.pdf legacy INVOICE #41'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '14'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '14' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 500, 500, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '15', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-08-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-08-01', ' 09:00:00'), 'PDF 15.pdf legacy INVOICE #51'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '15'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '15' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '16', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-08-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-08-01', ' 09:00:00'), 'PDF 16.pdf legacy INVOICE #16'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '16'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '16' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '18', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-10-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-10-01', ' 09:00:00'), 'PDF 18.pdf legacy INVOICE #18'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '18'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '18' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '19', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-02-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-02-01', ' 09:00:00'), 'PDF 19.pdf legacy INVOICE #91'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '19'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '19' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '20', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-01-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-01-01', ' 09:00:00'), 'PDF 20.pdf legacy INVOICE #20'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '20'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '20' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '21', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-02-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-02-01', ' 09:00:00'), 'PDF 21.pdf legacy INVOICE #21'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '21'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '21' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '22', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-03-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-03-01', ' 09:00:00'), 'PDF 22.pdf legacy INVOICE #22'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '22'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '22' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '23', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-04-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-04-01', ' 09:00:00'), 'PDF 23.pdf legacy INVOICE #32'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '23'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '23' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '24', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-05-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-05-01', ' 09:00:00'), 'PDF 24.pdf legacy INVOICE #42'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '24'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '24' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '25', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-05-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-05-01', ' 09:00:00'), 'PDF 25.pdf legacy INVOICE #52'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '25'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '25' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '26', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-05-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-05-01', ' 09:00:00'), 'PDF 26.pdf legacy INVOICE #62'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '26'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '26' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '27', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-05-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-05-01', ' 09:00:00'), 'PDF 27.pdf legacy INVOICE #72'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '27'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '27' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '28', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-09-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-09-01', ' 09:00:00'), 'PDF 28.pdf legacy INVOICE #82'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '28'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '28' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '29', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-01-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-01-01', ' 09:00:00'), 'PDF 29.pdf legacy INVOICE #29'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '29'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '29' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '30', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2025-01-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2025-01-01', ' 09:00:00'), 'PDF 30.pdf legacy INVOICE #30'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '30'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '30' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '31', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-01-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-01-01', ' 09:00:00'), 'PDF 31.pdf legacy INVOICE #13'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '31'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '31' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '32', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-02-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-02-01', ' 09:00:00'), 'PDF 32.pdf legacy INVOICE #32'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '32'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '32' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '33', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-03-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-03-01', ' 09:00:00'), 'PDF 33.pdf legacy INVOICE #33'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '33'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '33' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '34', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2026-03-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2026-03-01', ' 09:00:00'), 'PDF 34.pdf legacy INVOICE #34'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '34'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '34' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '35', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-06-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-06-01', ' 09:00:00'), 'PDF 35.pdf legacy INVOICE #35'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '35'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '35' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '36', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-07-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-07-01', ' 09:00:00'), 'PDF 36.pdf legacy INVOICE #36'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '36'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '36' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '37', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-08-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-08-01', ' 09:00:00'), 'PDF 37.pdf legacy INVOICE #37'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '37'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '37' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '38', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-09-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-09-01', ' 09:00:00'), 'PDF 38.pdf legacy INVOICE #38'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '38'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '38' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '39', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-10-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-10-01', ' 09:00:00'), 'PDF 39.pdf legacy INVOICE #39'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '39'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '39' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

INSERT INTO invoices (
  invoice_number, contract_id, customer_name, service_type, invoice_date,
  total_amount, amount_in_words, language,
  include_stamp, include_signature, status, sent_at, error_message
)
SELECT
  '40', @c2, 'الشركة الوطنية المتحدة مول الجادرية', 'Pest control', '2024-11-01',
  1000000, NULL, 'en', 1, 1, 'sent',
  CONCAT('2024-11-01', ' 09:00:00'), 'PDF 40.pdf legacy INVOICE #40'
WHERE @c2 IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoices WHERE invoice_number = '40'
);
SET @inv = (SELECT id FROM invoices WHERE invoice_number = '40' LIMIT 1);
INSERT INTO invoice_lines (invoice_id, description, service_type, units_count, units_label, unit_price, line_total, sort_order)
SELECT @inv, 'Pest control at Al-Jadriya Mall', 'Pest control', 1.000, 'One month', 1000000, 1000000, 0
WHERE @inv IS NOT NULL AND NOT EXISTS (
  SELECT 1 FROM invoice_lines WHERE invoice_id = @inv
);

SELECT @c2 AS contract_id,
  (SELECT COUNT(*) FROM invoices WHERE contract_id = @c2) AS invoices_count,
  'الشركة الوطنية المتحدة مول الجادرية' AS customer_name;

-- فرض الإنجليزية على كل العقود والفواتير الموجودة

-- وصف البند كما في الفاتورة الأصلية: بدون عبارة «شهر واحد» (الكمية = 1)
UPDATE invoice_lines
SET description = REPLACE(description, ' — شهر واحد', '')
WHERE description LIKE '%شهر واحد%';
UPDATE invoice_lines
SET description = REPLACE(description, ' شهر واحد', '')
WHERE description LIKE '%شهر واحد%';
UPDATE contract_sites
SET site_name = REPLACE(REPLACE(site_name, ' — شهر واحد', ''), ' شهر واحد', '')
WHERE site_name LIKE '%شهر واحد%';
UPDATE invoice_lines
SET units_label = 'One month'
WHERE (units_label IS NULL OR units_label = '')
  AND description LIKE '%مول الجادرية%';
UPDATE contract_sites
SET units_label = 'One month'
WHERE (units_label IS NULL OR units_label = '')
  AND site_name LIKE '%مول الجادرية%';
UPDATE contracts SET invoice_language = 'en' WHERE invoice_language <> 'en';
UPDATE invoices SET language = 'en' WHERE language <> 'en';

SET FOREIGN_KEY_CHECKS = 1;
