SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS simeya_business_schema_migrations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  migration_key VARCHAR(120) NOT NULL,
  migration_name VARCHAR(255) NOT NULL,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_migration_key (migration_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_types (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type_key VARCHAR(100) NOT NULL,
  type_name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  icon_class VARCHAR(120) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 1,
  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,
  PRIMARY KEY (id), UNIQUE KEY uq_type_key (type_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_subtypes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  business_type_id BIGINT UNSIGNED NOT NULL,
  subtype_key VARCHAR(120) NOT NULL,
  subtype_name VARCHAR(180) NOT NULL,
  description TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 1,
  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,
  PRIMARY KEY (id), UNIQUE KEY uq_business_subtype (business_type_id, subtype_key),
  CONSTRAINT fk_business_subtypes_type FOREIGN KEY (business_type_id) REFERENCES simeya_business_types(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_organizations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_code VARCHAR(40) NOT NULL,
  organization_name VARCHAR(200) NOT NULL,
  slug VARCHAR(200) NOT NULL,
  business_type_id BIGINT UNSIGNED NULL,
  business_subtype_id BIGINT UNSIGNED NULL,
  description TEXT NULL,
  mission TEXT NULL,
  website_url VARCHAR(500) NULL,
  public_email VARCHAR(255) NULL,
  phone VARCHAR(80) NULL,
  country_code VARCHAR(10) NULL,
  country_name VARCHAR(120) NULL,
  state_region VARCHAR(160) NULL,
  city VARCHAR(160) NULL,
  address_line_1 VARCHAR(255) NULL,
  address_line_2 VARCHAR(255) NULL,
  postal_code VARCHAR(40) NULL,
  logo_url VARCHAR(1000) NULL,
  banner_url VARCHAR(1000) NULL,
  owner_user_id BIGINT UNSIGNED NOT NULL,
  finder_user_id BIGINT UNSIGNED NULL,
  status ENUM('draft','active','inactive','suspended','archived') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_org_code (organization_code), UNIQUE KEY uq_org_slug (slug),
  KEY idx_org_owner (owner_user_id), KEY idx_org_type_status (business_type_id,status),
  CONSTRAINT fk_org_type FOREIGN KEY (business_type_id) REFERENCES simeya_business_types(id) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_org_subtype FOREIGN KEY (business_subtype_id) REFERENCES simeya_business_subtypes(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_organization_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role_key ENUM('owner','admin','manager','staff','marketing','sales','finance','viewer') NOT NULL DEFAULT 'staff',
  relationship_type ENUM('owner','employee','contractor','agent','advisor','partner','other') NOT NULL DEFAULT 'employee',
  is_owner TINYINT(1) NOT NULL DEFAULT 0,
  is_primary_contact TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('invited','active','inactive','removed') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_org_user (organization_id,user_id), KEY idx_org_users_user (user_id,status),
  CONSTRAINT fk_org_users_org FOREIGN KEY (organization_id) REFERENCES simeya_business_organizations(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_locations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_name VARCHAR(180) NOT NULL,
  location_type ENUM('head_office','branch','store','property','warehouse','factory','office','other') NOT NULL DEFAULT 'other',
  public_email VARCHAR(255) NULL, phone VARCHAR(80) NULL,
  country_code VARCHAR(10) NULL, country_name VARCHAR(120) NULL, state_region VARCHAR(160) NULL, city VARCHAR(160) NULL,
  address_line_1 VARCHAR(255) NULL, address_line_2 VARCHAR(255) NULL, postal_code VARCHAR(40) NULL,
  latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_locations_org (organization_id,status,is_primary),
  CONSTRAINT fk_locations_org FOREIGN KEY (organization_id) REFERENCES simeya_business_organizations(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_modules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  module_key VARCHAR(100) NOT NULL, module_name VARCHAR(160) NOT NULL, description TEXT NULL,
  route_prefix VARCHAR(255) NULL, icon_class VARCHAR(120) NULL, sort_order INT NOT NULL DEFAULT 0,
  is_core 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,
  PRIMARY KEY(id), UNIQUE KEY uq_module_key(module_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_organization_modules (
  organization_id BIGINT UNSIGNED NOT NULL, module_id BIGINT UNSIGNED NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  source ENUM('system','business_type','admin','user','migration') NOT NULL DEFAULT 'system',
  enabled_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(organization_id,module_id),
  CONSTRAINT fk_org_modules_org FOREIGN KEY (organization_id) REFERENCES simeya_business_organizations(id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_org_modules_module FOREIGN KEY (module_id) REFERENCES simeya_business_modules(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS simeya_business_legacy_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NULL,
  source_platform ENUM('extranet','tv','ctn','marketing','advertising','aitpa','other') NOT NULL,
  source_database VARCHAR(160) NULL, source_table VARCHAR(160) NOT NULL, source_id VARCHAR(120) NOT NULL,
  new_entity_type VARCHAR(120) NULL, new_entity_id BIGINT UNSIGNED NULL,
  migration_status ENUM('pending','mapped','migrated','verified','skipped','error') NOT NULL DEFAULT 'pending',
  migration_notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, migrated_at DATETIME NULL,
  PRIMARY KEY(id), UNIQUE KEY uq_legacy_source(source_platform,source_table,source_id),
  CONSTRAINT fk_legacy_org FOREIGN KEY (organization_id) REFERENCES simeya_business_organizations(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO simeya_business_types(type_key,type_name,description,icon_class,sort_order,is_system,is_active) VALUES
('general','General Business','General business or organization.','fa-solid fa-building',10,1,1),
('hotel_resort','Hotel / Resort','Accommodation, hotel, resort or hospitality property.','fa-solid fa-hotel',20,1,1),
('restaurant','Restaurant / Dining','Restaurant, cafe or food service business.','fa-solid fa-utensils',30,1,1),
('tour_operator','Tour Operator','Tours, attractions and activities.','fa-solid fa-route',40,1,1),
('manufacturer','Manufacturer','Manufacturing and industrial production.','fa-solid fa-industry',50,1,1),
('exporter','Exporter','Export-oriented business.','fa-solid fa-ship',60,1,1),
('retailer','Retailer','Retail and commerce business.','fa-solid fa-store',70,1,1),
('professional_services','Professional Services','Professional and business services.','fa-solid fa-briefcase',80,1,1),
('media','Media Company','Media, television or content business.','fa-solid fa-photo-film',90,1,1),
('nonprofit','Nonprofit / Community','Nonprofit, charity or community organization.','fa-solid fa-hand-holding-heart',100,1,1)
ON DUPLICATE KEY UPDATE type_name=VALUES(type_name),description=VALUES(description),icon_class=VALUES(icon_class),sort_order=VALUES(sort_order),is_active=1;

INSERT INTO simeya_business_modules(module_key,module_name,description,route_prefix,icon_class,sort_order,is_core,is_active) VALUES
('business','My Business','Business profile, team and locations.','/','fa-solid fa-building',10,1,1),
('website','Website','Website pages, content and settings.','/website','fa-solid fa-globe',20,0,1),
('media','Media','YouTube, video, images and playlists.','/media','fa-solid fa-photo-film',30,0,1),
('inventory','Inventory','Products, properties, rooms, tours and inventory.','/inventory','fa-solid fa-boxes-stacked',40,0,1),
('marketing','Marketing','Contacts, campaigns and analytics.','/marketing','fa-solid fa-bullhorn',50,0,1),
('advertising','Advertising','Campaigns, media and placements.','/advertising','fa-solid fa-rectangle-ad',60,0,1),
('sales','Sales / Bookings','Leads, sales, orders and bookings.','/sales','fa-solid fa-cart-shopping',70,0,1),
('commissions','Commissions & Rewards','Finder fees, commissions and bonuses.','/commissions','fa-solid fa-coins',80,0,1),
('reports','Reports','Cross-module reporting and analytics.','/reports','fa-solid fa-chart-column',90,0,1),
('ai','AI','AI content and business intelligence.','/ai','fa-solid fa-wand-magic-sparkles',100,0,1)
ON DUPLICATE KEY UPDATE module_name=VALUES(module_name),description=VALUES(description),route_prefix=VALUES(route_prefix),icon_class=VALUES(icon_class),sort_order=VALUES(sort_order),is_active=1;

INSERT IGNORE INTO simeya_business_schema_migrations(migration_key,migration_name) VALUES ('001_phase1_foundation','Phase 1 business foundation');