-- Safe, additive reconciliation for an existing MySQL/MariaDB database.
-- This script intentionally has no USE, DROP, TRUNCATE, or database-qualified
-- names, so it can be run against any selected MemberSync database.

CREATE TABLE IF NOT EXISTS schema_migrations (
  migration_name VARCHAR(190) NOT NULL PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes VARCHAR(500) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_methods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  instructions TEXT NULL,
  is_online TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_payment_methods_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO payment_methods (code,name,is_online,is_active,sort_order) VALUES
 ('paychangu','PayChangu',1,1,10),('bank_transfer','Bank transfer',0,1,20),
 ('cash','Cash',0,1,30),('card','Card / POS',0,1,40),
 ('mobile_money','Mobile money',0,1,50);

CREATE TABLE IF NOT EXISTS document_templates (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  document_type ENUM('ticket','receipt','invoice','quote','membership_id','membership_certificate','event_certificate','event_nametag') NOT NULL,
  name VARCHAR(160) NOT NULL,
  page_format VARCHAR(30) NOT NULL DEFAULT 'A4',
  orientation ENUM('P','L') NOT NULL DEFAULT 'P',
  header_image_url VARCHAR(1000) NULL,
  footer_image_url VARCHAR(1000) NULL,
  template_html MEDIUMTEXT NOT NULL,
  custom_css MEDIUMTEXT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_document_template_type (document_type,is_active,is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'ticket','Standard event ticket','A5','L','<h1>{{event_title}}</h1><p>{{user_name}}</p><strong>{{ticket_code}}</strong>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='ticket' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'receipt','Standard payment receipt','A5','P','<h1>Receipt</h1><p>{{user_name}}</p><p>{{receipt_num}}</p><strong>MWK {{amount_paid}}</strong>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='receipt' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'invoice','Standard invoice','A4','P','<h1>Invoice</h1><p>{{member_name}}</p><p>{{document_number}}</p>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='invoice' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'quote','Standard quote','A4','P','<h1>Quotation</h1><p>{{member_name}}</p><p>{{document_number}}</p>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='quote' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'membership_id','Standard membership ID','85.6mm,54mm','L','<div>{{member_name}}<br>{{membership_id}}</div>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='membership_id' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'membership_certificate','Standard membership certificate','A4','L','<h1>Membership Certificate</h1><h2>{{member_name}}</h2>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='membership_certificate' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'event_certificate','Standard event certificate','A4','L','<h1>Certificate of Participation</h1><h2>{{member_name}}</h2><p>{{event_name}}</p>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='event_certificate' AND is_active=1);
INSERT INTO document_templates(document_type,name,page_format,orientation,template_html,custom_css,is_default,is_active)
SELECT 'event_nametag','Standard event nametag','A6','P','<div><h2>{{member_name}}</h2><p>{{event_name}}</p><strong>{{ticket_code}}</strong></div>','',1,1
WHERE NOT EXISTS (SELECT 1 FROM document_templates WHERE document_type='event_nametag' AND is_active=1);

INSERT INTO schema_migrations(migration_name,notes)
VALUES ('20260903_safe_cross_database_reconciliation','Additive tables and default templates; safe to rerun')
ON DUPLICATE KEY UPDATE applied_at=CURRENT_TIMESTAMP;