-- TradesIreland.ie MVP schema (MySQL 8+)
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS business_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(180) NOT NULL,
  phone VARCHAR(80) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_business_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS counties (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  seo_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(500) DEFAULT NULL,
  intro_text MEDIUMTEXT DEFAULT NULL,
  icon_path VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_counties_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trade_types (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  seo_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_trade_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(255) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  county_id INT UNSIGNED NOT NULL,
  trade_type_id INT UNSIGNED NOT NULL,
  town_area VARCHAR(180) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  latitude DECIMAL(10,7) DEFAULT NULL,
  longitude DECIMAL(11,7) DEFAULT NULL,
  phone VARCHAR(80) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  website_url VARCHAR(500) DEFAULT NULL,
  short_description VARCHAR(500) DEFAULT NULL,
  full_description MEDIUMTEXT DEFAULT NULL,
  services_offered MEDIUMTEXT DEFAULT NULL,
  qualifications TEXT DEFAULT NULL,
  logo_path VARCHAR(500) DEFAULT NULL,
  featured_image_path VARCHAR(500) DEFAULT NULL,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  is_frontpage_featured TINYINT(1) NOT NULL DEFAULT 0,
  is_premium TINYINT(1) NOT NULL DEFAULT 0,
  featured_rank INT NOT NULL DEFAULT 100,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','draft') NOT NULL DEFAULT 'draft',
  first_activated_at TIMESTAMP NULL DEFAULT NULL,
  business_user_id INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_listing_county (county_id),
  KEY idx_listing_trade (trade_type_id),
  KEY idx_listing_status (status),
  KEY idx_listing_featured (is_featured, featured_rank),
  KEY idx_listing_owner (business_user_id),
  KEY idx_listing_first_activation (first_activated_at),
  CONSTRAINT fk_listing_county FOREIGN KEY (county_id) REFERENCES counties(id) ON DELETE RESTRICT,
  CONSTRAINT fk_listing_trade FOREIGN KEY (trade_type_id) REFERENCES trade_types(id) ON DELETE RESTRICT,
  CONSTRAINT fk_listing_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_subscriptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED NOT NULL,
  stripe_customer_id VARCHAR(255) DEFAULT NULL,
  stripe_subscription_id VARCHAR(255) DEFAULT NULL UNIQUE,
  status VARCHAR(40) NOT NULL DEFAULT 'incomplete',
  current_period_start DATETIME DEFAULT NULL,
  current_period_end DATETIME DEFAULT NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_listing_subscription_listing (listing_id),
  KEY idx_listing_subscription_status (status),
  KEY idx_listing_subscription_business (business_user_id),
  CONSTRAINT fk_listing_subscription_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_listing_subscription_business FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_payment_links (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED DEFAULT NULL,
  admin_id INT UNSIGNED DEFAULT NULL,
  contact_email VARCHAR(255) DEFAULT NULL,
  addon_keys_csv VARCHAR(120) NOT NULL,
  addon_amounts_json TEXT DEFAULT NULL,
  custom_total_cents INT UNSIGNED NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'eur',
  token VARCHAR(64) NOT NULL UNIQUE,
  status ENUM('pending','checkout_started','paid','cancelled','expired') NOT NULL DEFAULT 'pending',
  stripe_checkout_session_id VARCHAR(255) DEFAULT NULL,
  stripe_subscription_id VARCHAR(255) DEFAULT NULL,
  paid_at DATETIME DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_listing_payment_links_listing (listing_id, created_at),
  KEY idx_listing_payment_links_status (status),
  CONSTRAINT fk_listing_payment_links_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_listing_payment_links_business FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL,
  CONSTRAINT fk_listing_payment_links_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_subscription_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_subscription_id INT UNSIGNED NOT NULL,
  addon_key ENUM('featured','frontpage_featured','premium') NOT NULL,
  monthly_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  stripe_item_id VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_listing_subscription_addon (listing_subscription_id, addon_key),
  KEY idx_listing_subscription_item_key (addon_key),
  CONSTRAINT fk_listing_subscription_item_subscription FOREIGN KEY (listing_subscription_id) REFERENCES listing_subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  event_type ENUM('view','website_click','quote_sent') NOT NULL,
  session_id VARCHAR(64) DEFAULT NULL,
  ip_hash CHAR(64) DEFAULT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_listing_event_lookup (listing_id, event_type, occurred_at),
  KEY idx_listing_event_occurred (occurred_at),
  CONSTRAINT fk_listing_event_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_quotes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED NOT NULL,
  customer_name VARCHAR(180) NOT NULL,
  customer_email VARCHAR(255) NOT NULL,
  message MEDIUMTEXT NOT NULL,
  status ENUM('new','in_progress','closed') NOT NULL DEFAULT 'new',
  business_note VARCHAR(1000) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_listing_quote_business_status (business_user_id, status, created_at),
  KEY idx_listing_quote_listing (listing_id, created_at),
  CONSTRAINT fk_listing_quote_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_listing_quote_business FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_claims (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED NOT NULL,
  message VARCHAR(1000) DEFAULT NULL,
  verification_token VARCHAR(64) DEFAULT NULL,
  verification_expires_at DATETIME DEFAULT NULL,
  email_verified_at TIMESTAMP NULL DEFAULT NULL,
  claimed_email VARCHAR(255) DEFAULT NULL,
  status ENUM('pending_email','pending','approved','rejected') NOT NULL DEFAULT 'pending_email',
  admin_note VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at TIMESTAMP NULL DEFAULT NULL,
  KEY idx_claim_listing (listing_id),
  KEY idx_claim_status (status),
  KEY idx_claim_user (business_user_id),
  UNIQUE KEY idx_claim_verify_token (verification_token),
  CONSTRAINT fk_claim_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
  CONSTRAINT fk_claim_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS listing_images (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  listing_id INT UNSIGNED NOT NULL,
  image_path VARCHAR(500) NOT NULL,
  alt_text VARCHAR(255) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_li_listing (listing_id),
  CONSTRAINT fk_li_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS county_trade_pages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  county_id INT UNSIGNED NOT NULL,
  trade_type_id INT UNSIGNED NOT NULL,
  seo_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(500) DEFAULT NULL,
  intro_text MEDIUMTEXT DEFAULT NULL,
  custom_content MEDIUMTEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_county_trade (county_id, trade_type_id),
  CONSTRAINT fk_ctp_county FOREIGN KEY (county_id) REFERENCES counties(id) ON DELETE CASCADE,
  CONSTRAINT fk_ctp_trade FOREIGN KEY (trade_type_id) REFERENCES trade_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blog_posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  excerpt TEXT DEFAULT NULL,
  content MEDIUMTEXT DEFAULT NULL,
  featured_image_path VARCHAR(500) DEFAULT NULL,
  meta_title VARCHAR(255) DEFAULT NULL,
  meta_description VARCHAR(500) DEFAULT NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_blog_status_pub (status, published_at),
  KEY idx_blog_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_placements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  placement_key VARCHAR(64) NOT NULL,
  title VARCHAR(255) DEFAULT NULL,
  image_path VARCHAR(500) DEFAULT NULL,
  target_url VARCHAR(1000) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ad_key_active (placement_key, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  placement_key VARCHAR(64) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  monthly_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  price_1m_cents INT UNSIGNED NOT NULL DEFAULT 0,
  price_3m_cents INT UNSIGNED NOT NULL DEFAULT 0,
  price_6m_cents INT UNSIGNED NOT NULL DEFAULT 0,
  price_12m_cents INT UNSIGNED NOT NULL DEFAULT 0,
  vat_rate_percent DECIMAL(5,2) NOT NULL DEFAULT 23.00,
  term_options_csv VARCHAR(32) NOT NULL DEFAULT '1,12',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  stripe_product_id VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ad_products_active (is_active, sort_order),
  KEY idx_ad_products_key (placement_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_product_id INT UNSIGNED NOT NULL,
  business_user_id INT UNSIGNED DEFAULT NULL,
  subscriber_name VARCHAR(180) NOT NULL,
  subscriber_email VARCHAR(255) NOT NULL,
  business_name VARCHAR(255) DEFAULT NULL,
  notes VARCHAR(1000) DEFAULT NULL,
  term_months INT UNSIGNED NOT NULL DEFAULT 1,
  subtotal_monthly_cents INT UNSIGNED NOT NULL DEFAULT 0,
  vat_monthly_cents INT UNSIGNED NOT NULL DEFAULT 0,
  total_monthly_cents INT UNSIGNED NOT NULL DEFAULT 0,
  workflow_status ENUM('awaiting_setup','creative_draft','awaiting_business_approval','approved_pending_charge','live','ended','cancelled') NOT NULL DEFAULT 'awaiting_setup',
  approval_token VARCHAR(64) DEFAULT NULL UNIQUE,
  approval_approved_at DATETIME DEFAULT NULL,
  ad_placement_id INT UNSIGNED DEFAULT NULL,
  stripe_setup_intent_id VARCHAR(255) DEFAULT NULL,
  stripe_payment_method_id VARCHAR(255) DEFAULT NULL,
  stripe_customer_id VARCHAR(255) DEFAULT NULL,
  stripe_subscription_id VARCHAR(255) DEFAULT NULL UNIQUE,
  status VARCHAR(40) NOT NULL DEFAULT 'incomplete',
  current_period_start DATETIME DEFAULT NULL,
  current_period_end DATETIME DEFAULT NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_ad_subs_status (status, current_period_end),
  KEY idx_ad_subs_email (subscriber_email),
  KEY idx_ad_subs_product (ad_product_id),
  KEY idx_ad_subs_business_user (business_user_id),
  KEY idx_ad_subs_workflow (workflow_status),
  KEY idx_ad_subs_placement (ad_placement_id),
  CONSTRAINT fk_ad_subs_product FOREIGN KEY (ad_product_id) REFERENCES ad_products(id) ON DELETE RESTRICT
  ,
  CONSTRAINT fk_ad_subs_business_user FOREIGN KEY (business_user_id) REFERENCES business_users(id) ON DELETE SET NULL
  ,
  CONSTRAINT fk_ad_subs_placement FOREIGN KEY (ad_placement_id) REFERENCES ad_placements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_revenue_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_subscription_id BIGINT UNSIGNED NOT NULL,
  stripe_invoice_id VARCHAR(255) DEFAULT NULL UNIQUE,
  amount_cents INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'eur',
  paid_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ad_revenue_paid_at (paid_at),
  KEY idx_ad_revenue_sub (ad_subscription_id),
  CONSTRAINT fk_ad_revenue_sub FOREIGN KEY (ad_subscription_id) REFERENCES ad_subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_placement_id INT UNSIGNED NOT NULL,
  event_type ENUM('view','click') NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ad_events_lookup (ad_placement_id, event_type, occurred_at),
  KEY idx_ad_events_occurred (occurred_at),
  CONSTRAINT fk_ad_events_placement FOREIGN KEY (ad_placement_id) REFERENCES ad_placements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  email VARCHAR(255) NOT NULL,
  message MEDIUMTEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(64) NOT NULL UNIQUE,
  value MEDIUMTEXT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;