CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','manager','agent') NOT NULL DEFAULT 'agent',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_code VARCHAR(100) NULL,
  name VARCHAR(190) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  type ENUM('customer','supplier','other') NOT NULL DEFAULT 'customer',
  notes TEXT NULL,
  consent_status ENUM('unknown','opted_in','opted_out') NOT NULL DEFAULT 'unknown',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_contacts_phone (phone),
  KEY idx_contacts_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(80) NOT NULL UNIQUE,
  title VARCHAR(150) NOT NULL,
  body TEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transaction_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id VARCHAR(190) NOT NULL UNIQUE,
  event_type VARCHAR(80) NOT NULL,
  contact_id BIGINT UNSIGNED NULL,
  transaction_number VARCHAR(120) NULL,
  payload JSON NOT NULL,
  status ENUM('queued','processing','processed','failed','skipped') NOT NULL DEFAULT 'queued',
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  KEY idx_events_status_created (status, created_at),
  CONSTRAINT fk_event_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NULL,
  direction ENUM('inbound','outbound') NOT NULL DEFAULT 'outbound',
  message_type VARCHAR(40) NOT NULL DEFAULT 'text',
  body TEXT NULL,
  provider_message_id VARCHAR(190) NULL,
  status ENUM('queued','sent','delivered','read','failed','received','unknown') NOT NULL DEFAULT 'queued',
  source_event_id VARCHAR(190) NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_messages_contact_created (contact_id, created_at),
  KEY idx_messages_status (status),
  CONSTRAINT fk_message_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id VARCHAR(190) NOT NULL UNIQUE,
  contact_id BIGINT UNSIGNED NULL,
  phone VARCHAR(30) NOT NULL,
  message_body TEXT NOT NULL,
  status ENUM('queued','processing','sent','failed') NOT NULL DEFAULT 'queued',
  attempts INT NOT NULL DEFAULT 0,
  available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_error TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_queue_due (status, available_at),
  CONSTRAINT fk_queue_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_keys (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  key_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME NULL,
  revoked_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS extension_instances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  instance_id VARCHAR(190) NOT NULL UNIQUE,
  connection_state ENUM('unknown','qr_required','connected') NOT NULL DEFAULT 'unknown',
  last_seen_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  details JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_created (created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO message_templates (event_type, title, body, enabled) VALUES
('sale.created','Sale confirmation','Hello {{customer_name}}, thank you for your business with Halozer Technologies.\nInvoice: {{number}}\nTotal: ₹{{total_amount}}\nPaid: ₹{{paid_amount}}\nBalance: ₹{{balance}}',1),
('payment.received','Payment received','Hello {{customer_name}}, we have recorded your payment of ₹{{amount}}. Thank you.',1),
('payment.reminder','Payment reminder','Hello {{customer_name}}, our records show an outstanding balance of ₹{{balance}}. Please contact us if you need a statement.',1);


CREATE TABLE IF NOT EXISTS webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_hash CHAR(64) NOT NULL UNIQUE,
  event_type VARCHAR(80) NOT NULL,
  provider_message_id VARCHAR(190) NULL,
  processing_status ENUM('processed','ignored','failed') NOT NULL DEFAULT 'processed',
  error_message TEXT NULL,
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_webhook_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;