SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS email_verifications;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS ticket_replies;
DROP TABLE IF EXISTS support_tickets;
DROP TABLE IF EXISTS task_items;
DROP TABLE IF EXISTS notification_logs;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS website_settings;
DROP TABLE IF EXISTS contact_messages;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS request_history;
DROP TABLE IF EXISTS request_notes;
DROP TABLE IF EXISTS appointments;
DROP TABLE IF EXISTS service_requests;
DROP TABLE IF EXISTS employee_profiles;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(50) DEFAULT '',
  country VARCHAR(100) DEFAULT '',
  timezone_label VARCHAR(80) DEFAULT 'Asia/Kolkata',
  address TEXT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('client','employee','admin') NOT NULL DEFAULT 'client',
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  email_verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE employee_profiles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  employee_code VARCHAR(30) NOT NULL,
  department VARCHAR(100) NOT NULL,
  designation VARCHAR(120) DEFAULT '',
  can_manage_payments TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_employee_profile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_employee_profile_user (user_id),
  UNIQUE KEY uniq_employee_code (employee_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE website_settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value LONGTEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE appointments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  full_name VARCHAR(150) DEFAULT '',
  email VARCHAR(190) DEFAULT '',
  phone VARCHAR(50) DEFAULT '',
  service_type VARCHAR(120) NOT NULL,
  appointment_date DATE NOT NULL,
  appointment_time TIME NOT NULL,
  timezone VARCHAR(100) NOT NULL,
  notes TEXT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'Booked',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_appointments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE service_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  service_category VARCHAR(120) NOT NULL,
  subject VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  assigned_employee_id INT UNSIGNED NULL,
  priority VARCHAR(20) NOT NULL DEFAULT 'Medium',
  status VARCHAR(30) NOT NULL DEFAULT 'Open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_requests_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_requests_employee FOREIGN KEY (assigned_employee_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id INT UNSIGNED NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_size INT UNSIGNED NOT NULL DEFAULT 0,
  mime_type VARCHAR(120) DEFAULT '',
  uploaded_by INT UNSIGNED NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_documents_request FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_documents_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE request_notes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id INT UNSIGNED NOT NULL,
  employee_id INT UNSIGNED NULL,
  note TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_request_notes_request FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_request_notes_user FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE request_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  details VARCHAR(255) DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_request_history_request FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_request_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contact_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(50) DEFAULT '',
  subject VARCHAR(180) DEFAULT '',
  message TEXT NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(60) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  request_id INT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(10) NOT NULL DEFAULT 'INR',
  due_date DATE NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'Unpaid',
  notes VARCHAR(255) DEFAULT '',
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_invoices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoices_request FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE SET NULL,
  CONSTRAINT fk_invoices_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  payment_method VARCHAR(60) NOT NULL,
  reference_no VARCHAR(120) DEFAULT '',
  paid_at DATE NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'initiated',
  notes TEXT NULL,
  added_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_added_by FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE task_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  assigned_to INT UNSIGNED NULL,
  related_request_id INT UNSIGNED NULL,
  due_date DATE NULL,
  priority VARCHAR(20) NOT NULL DEFAULT 'Medium',
  status VARCHAR(30) NOT NULL DEFAULT 'Pending',
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_task_items_assigned_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_items_request FOREIGN KEY (related_request_id) REFERENCES service_requests(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_items_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_tickets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  subject VARCHAR(180) NOT NULL,
  category VARCHAR(120) NOT NULL DEFAULT 'General Support',
  priority VARCHAR(20) NOT NULL DEFAULT 'Medium',
  status VARCHAR(30) NOT NULL DEFAULT 'Open',
  assigned_employee_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_support_tickets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_support_tickets_employee FOREIGN KEY (assigned_employee_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ticket_replies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  message TEXT NOT NULL,
  is_internal TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ticket_replies_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
  CONSTRAINT fk_ticket_replies_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel VARCHAR(30) NOT NULL,
  target VARCHAR(190) NOT NULL,
  subject VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  related_type VARCHAR(60) DEFAULT '',
  related_id INT UNSIGNED NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'logged',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_password_reset_token (token_hash),
  KEY idx_password_resets_user (user_id),
  CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_verifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_email_verification_token (token_hash),
  KEY idx_email_verifications_user (user_id),
  CONSTRAINT fk_email_verifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO users (id, full_name, email, phone, country, timezone_label, address, password_hash, role, status, email_verified_at, created_at) VALUES
  (1, 'Ravi Kumar', 'ravi.kumar@example.com', '+1 408 555 1101', 'USA', 'America/Los_Angeles', 'San Jose, California, USA', '$2y$12$PGOnXjBZ9qJ0GihWkhvSku1NomagPUbwrLlaX3vqpHWYLP9vdyN1q', 'client', 'active', NOW(), '2026-04-01 09:00:00'),
  (2, 'Lakshmi Priya', 'lakshmi.priya@example.com', '+44 20 7946 1200', 'UK', 'Europe/London', 'London, UK', '$2y$12$PGOnXjBZ9qJ0GihWkhvSku1NomagPUbwrLlaX3vqpHWYLP9vdyN1q', 'client', 'active', NOW(), '2026-04-02 10:15:00'),
  (3, 'Srinivas Rao', 'employee@nriserv.demo', '+91 82978 26789', 'India', 'Asia/Kolkata', 'Guntur, Andhra Pradesh', '$2y$12$1Nkm17T0HCNzhF05HvaTBu.TvyW5z2Xa23F22fQPivDXiB77bAUJa', 'employee', 'active', NOW(), '2026-04-01 08:30:00'),
  (4, 'Portal Admin', 'admin@nriserv.demo', '+91 90000 00000', 'India', 'Asia/Kolkata', 'Hyderabad, Telangana', '$2y$12$uA01kiQyHNABIJJyM5t4Ze.DkXQKcyH7uLOhCRPPnvzxFaiZ0rPUm', 'admin', 'active', NOW(), '2026-04-01 08:00:00');

INSERT INTO employee_profiles (id, user_id, employee_code, department, designation, can_manage_payments, created_at) VALUES
  (1, 3, 'EMP-1001', 'Client Relations', 'Senior Case Manager', 1, '2026-04-01 08:35:00'),
  (2, 4, 'ADM-0001', 'Administration', 'Portal Administrator', 1, '2026-04-01 08:05:00');

INSERT INTO website_settings (setting_key, setting_value) VALUES
('site_title','NRISERV'),('logo_prefix','NRI'),('logo_suffix','SERV'),('tagline','Trusted NRI Services for Telangana & Andhra Pradesh'),('primary_phone','+91-82978-26789'),('primary_email','info@nriserv.com'),('whatsapp_number','918297826789'),('company_address','Hyderabad & Guntur, India'),('support_hours','24×7 assistance'),('hero_heading','NRI Services Made Simple From Anywhere in the World'),('hero_subheading','Documents, property, taxation, immigration, elder care, and support services for NRIs across Telangana and Andhra Pradesh.'),('hero_primary_button_text','Start Your Request'),('hero_primary_button_link','login.php'),('hero_secondary_button_text','Book Appointment'),('hero_secondary_button_link','appointment.php'),('footer_about','End-to-end support for OCI, passports, property, tax, legal, and family support services for NRIs.'),('seo_home_title','NRISERV — Trusted NRI Services for Telangana & Andhra Pradesh'),('seo_home_description','Trusted NRI services for Telangana and Andhra Pradesh.'),('maintenance_mode','0'),('razorpay_enabled','0'),('razorpay_key_id',''),('razorpay_key_secret',''),('cashfree_enabled','0'),('cashfree_app_id',''),('cashfree_secret_key',''),('cashfree_environment','sandbox'),('stripe_enabled','0'),('stripe_publishable_key',''),('stripe_secret_key',''),('stripe_webhook_secret',''),('paypal_enabled','0'),('paypal_client_id',''),('paypal_client_secret',''),('paypal_environment','sandbox'),('upi_id','nriserv@upi'),('upi_name','NRISERV'),('whatsapp_webhook_url',''),('whatsapp_webhook_token',''),('site_url',''),('smtp_host',''),('smtp_port','587'),('smtp_username',''),('smtp_password',''),('smtp_encryption','tls'),('smtp_from_email',''),('smtp_from_name','NRISERV');

INSERT INTO service_requests (id, user_id, service_category, subject, description, assigned_employee_id, priority, status, created_at, updated_at) VALUES
  (1001, 1, 'OCI Card Renewal', 'Adult OCI renewal for US passport holder', 'Client needs OCI renewal support including checklist review, document validation, and appointment guidance.', 3, 'High', 'In Progress', '2026-04-03 11:00:00', '2026-04-09 14:30:00'),
  (1002, 1, 'Property Management', 'Tenant follow-up for Hyderabad flat', 'Client wants rent follow-up, maintenance inspection, and monthly reporting for existing tenant.', 3, 'Medium', 'Open', '2026-04-04 16:20:00', '2026-04-08 12:00:00'),
  (1003, 2, 'Passport Renewal', 'Indian passport renewal from UK', 'Client needs help with passport renewal, VFS documentation, and address proof clarification.', 3, 'High', 'Review', '2026-04-05 10:45:00', '2026-04-09 18:10:00'),
  (1004, 2, 'Education Support', 'Engineering admission counseling', 'Client requested support for BTech admission options in Hyderabad for a dependent student.', 4, 'Low', 'Completed', '2026-04-02 13:00:00', '2026-04-07 17:00:00');

INSERT INTO request_notes (id, request_id, employee_id, note, created_at) VALUES
  (1, 1001, 3, 'Collected passport copy and current OCI card. Waiting for signed declaration form.', '2026-04-07 09:30:00'),
  (2, 1001, 3, 'Checklist reviewed and photo specifications shared with the client.', '2026-04-08 15:20:00'),
  (3, 1002, 3, 'Spoke with tenant and requested pending maintenance confirmation by Friday.', '2026-04-08 11:05:00'),
  (4, 1003, 3, 'Passport application draft reviewed. Need updated UK address proof.', '2026-04-09 17:40:00'),
  (5, 1004, 4, 'Admission shortlist sent to client and counseling call completed.', '2026-04-07 16:10:00');

INSERT INTO request_history (id, request_id, user_id, action, details, created_at) VALUES
  (1, 1001, 1, 'Request created', 'Case created from client dashboard', '2026-04-03 11:00:00'),
  (2, 1001, 3, 'Status changed', 'Open → In Progress', '2026-04-07 09:30:00'),
  (3, 1002, 1, 'Request created', 'Case created from client dashboard', '2026-04-04 16:20:00'),
  (4, 1003, 2, 'Request created', 'Case created from client dashboard', '2026-04-05 10:45:00'),
  (5, 1004, 4, 'Status changed', 'Open → Completed', '2026-04-07 17:00:00');

INSERT INTO documents (id, request_id, file_name, stored_name, file_path, file_size, mime_type, uploaded_by, uploaded_at) VALUES
  (1, 1001, 'passport-copy.pdf', 'sample-passport-copy.pdf', 'uploads/documents/sample-passport-copy.pdf', 245760, 'application/pdf', 1, '2026-04-07 09:45:00'),
  (2, 1002, 'property-photo.jpg', 'sample-property-photo.jpg', 'uploads/documents/sample-property-photo.jpg', 185320, 'image/jpeg', 1, '2026-04-08 11:20:00');

INSERT INTO appointments (id, user_id, full_name, email, phone, service_type, appointment_date, appointment_time, timezone, notes, status, created_at) VALUES
  (1, 1, 'Ravi Kumar', 'ravi.kumar@example.com', '+1 408 555 1101', 'OCI Consultation', '2026-04-15', '18:30:00', 'America/Los_Angeles', 'Need help understanding renewal steps and document scan quality.', 'Booked', '2026-04-09 12:15:00'),
  (2, 2, 'Lakshmi Priya', 'lakshmi.priya@example.com', '+44 20 7946 1200', 'Passport Renewal Consultation', '2026-04-16', '14:00:00', 'Europe/London', 'Would like VFS checklist review before submission.', 'Confirmed', '2026-04-09 14:45:00');

INSERT INTO contact_messages (id, name, email, phone, subject, message, status, created_at) VALUES
  (1, 'Anita Reddy', 'anita.reddy@example.com', '+971 50 555 7788', 'Need help with PCC', 'I need police clearance certificate assistance for my Canada PR process. Please share required documents and timeline.', 'new', '2026-04-08 10:20:00'),
  (2, 'Kiran Varma', 'kiran.varma@example.com', '+1 646 555 2233', 'Property verification', 'Looking for legal and technical verification before buying a flat in Hyderabad. Please call this weekend.', 'contacted', '2026-04-09 13:25:00');

INSERT INTO invoices (id, invoice_no, user_id, request_id, title, amount, currency, due_date, status, notes, created_by, created_at, updated_at) VALUES
  (1, 'INV-20260410-001', 1, 1001, 'OCI Renewal Service Fee', 7500.00, 'INR', '2026-04-20', 'Part Paid', 'Initial processing fee', 4, '2026-04-10 10:00:00', '2026-04-10 12:00:00'),
  (2, 'INV-20260410-002', 2, 1003, 'Passport Renewal Support', 120.00, 'GBP', '2026-04-22', 'Unpaid', 'VFS checklist and filing support', 4, '2026-04-10 10:15:00', '2026-04-10 10:15:00');

INSERT INTO payments (id, invoice_id, user_id, amount, payment_method, reference_no, paid_at, status, notes, added_by, created_at) VALUES
  (1, 1, 1, 3000.00, 'Bank Transfer', 'UTR9988123', '2026-04-10', 'verified', 'Initial transfer received', 3, '2026-04-10 12:00:00');

INSERT INTO notification_logs (id, channel, target, subject, message, related_type, related_id, status, created_at) VALUES
  (1, 'email', 'ravi.kumar@example.com', 'Invoice created', 'Your OCI renewal invoice has been generated.', 'invoice', 1, 'queued', '2026-04-10 10:01:00'),
  (2, 'whatsapp', '+14085551101', 'Case updated', 'Your OCI renewal case has moved to In Progress.', 'request', 1001, 'logged', '2026-04-10 10:12:00');