-- ProSensia Cloud Schema — MySQL 5.7 / 8.0 compatible
-- Run once on first deploy; safe to re-run (all CREATE TABLE use IF NOT EXISTS).

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
  id            VARCHAR(36)  NOT NULL,
  email         VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT 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_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_roles (
  id         VARCHAR(36) NOT NULL,
  user_id    VARCHAR(36) NOT NULL,
  role       VARCHAR(50) NOT NULL,
  created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_role (user_id, role),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS profiles (
  id                   VARCHAR(36)  NOT NULL,
  email                VARCHAR(255) NULL,
  full_name            VARCHAR(255) NULL,
  account_type         VARCHAR(50)  NULL,
  organization_name    VARCHAR(255) NULL,
  phone                VARCHAR(50)  NULL,
  city                 VARCHAR(100) NULL,
  country              VARCHAR(100) NULL,
  logo_url             VARCHAR(500) NULL,
  onboarding_completed TINYINT      NOT NULL DEFAULT 0,
  onboarding_step      VARCHAR(100) NULL,
  created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT fk_profiles_user FOREIGN KEY (id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
  id               VARCHAR(36)  NOT NULL,
  user_id          VARCHAR(36)  NOT NULL,
  name             VARCHAR(255) NOT NULL,
  device_type      VARCHAR(50)  NOT NULL DEFAULT 'light',
  mac_address      VARCHAR(20)  NULL,
  building_id      VARCHAR(36)  NULL,
  venue_id         VARCHAR(36)  NULL,
  room_id          VARCHAR(36)  NULL,
  is_on            TINYINT      NOT NULL DEFAULT 1,
  rated_power_w    INT          NOT NULL DEFAULT 60,
  current_power_w  INT          NOT NULL DEFAULT 0,
  status           VARCHAR(20)  NOT NULL DEFAULT 'offline',
  health_score     INT          NOT NULL DEFAULT 95,
  schedule_mode    VARCHAR(20)  NOT NULL DEFAULT 'manual',
  metadata         LONGTEXT     NULL,
  firmware_version VARCHAR(50)  NULL,
  last_seen_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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_devices_user (user_id),
  UNIQUE KEY uq_devices_user_mac (user_id, mac_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_telemetry (
  id             INT      NOT NULL AUTO_INCREMENT,
  user_id        VARCHAR(36) NOT NULL,
  device_id      VARCHAR(36) NOT NULL,
  is_on          TINYINT     NOT NULL DEFAULT 0,
  power_w        INT         NOT NULL DEFAULT 0,
  energy_kwh     DOUBLE      NOT NULL DEFAULT 0,
  temperature_c  DOUBLE      NULL,
  humidity       DOUBLE      NULL,
  temp_outlet    DOUBLE      NULL,
  temp_condenser DOUBLE      NULL,
  current_amps   DOUBLE      NULL,
  power_factor   DOUBLE      NULL,
  meta           LONGTEXT    NULL,
  recorded_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tel_user_time   (user_id, recorded_at),
  KEY idx_tel_device_time (device_id, recorded_at),
  KEY idx_tel_env         (device_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_telemetry_hourly (
  device_id   VARCHAR(36)  NOT NULL,
  user_id     VARCHAR(36)  NOT NULL,
  bucket      VARCHAR(20)  NOT NULL,
  avg_power_w DOUBLE       NOT NULL,
  energy_kwh  DOUBLE       NOT NULL,
  samples     INT          NOT NULL,
  PRIMARY KEY (device_id, bucket)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_telemetry_daily (
  device_id   VARCHAR(36) NOT NULL,
  user_id     VARCHAR(36) NOT NULL,
  bucket      VARCHAR(10) NOT NULL,
  avg_power_w DOUBLE      NOT NULL,
  energy_kwh  DOUBLE      NOT NULL,
  samples     INT         NOT NULL,
  PRIMARY KEY (device_id, bucket)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_schedules (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  device_id    VARCHAR(36)  NULL,
  target_type  VARCHAR(30)  NOT NULL DEFAULT 'device',
  target_value VARCHAR(100) NULL,
  rule_kind    VARCHAR(30)  NOT NULL DEFAULT 'custom',
  priority     INT          NOT NULL DEFAULT 100,
  days_of_week VARCHAR(30)  NOT NULL DEFAULT '[]',
  start_time   VARCHAR(5)   NOT NULL,
  end_time     VARCHAR(5)   NOT NULL,
  action       VARCHAR(10)  NOT NULL DEFAULT 'on',
  enabled      TINYINT      NOT NULL DEFAULT 1,
  notes        TEXT         NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS master_overrides (
  id              VARCHAR(36)  NOT NULL,
  user_id         VARCHAR(36)  NOT NULL,
  master_on       TINYINT      NOT NULL DEFAULT 1,
  building_states LONGTEXT     NULL,
  extra_state     LONGTEXT     NULL,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_master_overrides_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS home_layouts (
  id         VARCHAR(36)  NOT NULL,
  user_id    VARCHAR(36)  NOT NULL,
  home_name  VARCHAR(255) NOT NULL DEFAULT 'My Home',
  rooms      LONGTEXT     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_home_layouts_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS org_setup_progress (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  org_name     VARCHAR(255) NOT NULL DEFAULT '',
  buildings    LONGTEXT     NULL,
  current_step INT          NOT NULL DEFAULT 0,
  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_setup_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS org_members (
  id             VARCHAR(36)  NOT NULL,
  owner_id       VARCHAR(36)  NOT NULL,
  member_user_id VARCHAR(36)  NULL,
  invite_email   VARCHAR(255) NOT NULL,
  role           VARCHAR(50)  NOT NULL DEFAULT 'viewer',
  status         VARCHAR(20)  NOT NULL DEFAULT 'pending',
  invited_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accepted_at    DATETIME     NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS member_permissions (
  id          VARCHAR(36) NOT NULL,
  owner_id    VARCHAR(36) NOT NULL,
  member_id   VARCHAR(36) NOT NULL,
  page_key    VARCHAR(100) NOT NULL,
  can_view    TINYINT     NOT NULL DEFAULT 1,
  can_edit    TINYINT     NOT NULL DEFAULT 0,
  can_control TINYINT     NOT NULL DEFAULT 0,
  can_chat    TINYINT     NOT NULL DEFAULT 0,
  can_notify  TINYINT     NOT NULL DEFAULT 0,
  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_member_perms (owner_id, member_id, page_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  id          VARCHAR(36)  NOT NULL,
  owner_id    VARCHAR(36)  NOT NULL,
  role        VARCHAR(50)  NOT NULL,
  page_key    VARCHAR(100) NOT NULL,
  can_view    TINYINT      NOT NULL DEFAULT 1,
  can_edit    TINYINT      NOT NULL DEFAULT 0,
  can_control TINYINT      NOT NULL DEFAULT 0,
  can_chat    TINYINT      NOT NULL DEFAULT 0,
  can_notify  TINYINT      NOT NULL DEFAULT 0,
  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_role_perms (owner_id, role, page_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id         VARCHAR(36)  NOT NULL,
  user_id    VARCHAR(36)  NOT NULL,
  type       VARCHAR(30)  NOT NULL DEFAULT 'info',
  title      VARCHAR(255) NOT NULL,
  body       TEXT         NULL,
  link       VARCHAR(255) NULL,
  `read`     TINYINT      NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notifications_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS billing_settings (
  id               VARCHAR(36)  NOT NULL,
  user_id          VARCHAR(36)  NOT NULL,
  country_code     VARCHAR(5)   NOT NULL DEFAULT 'PK',
  provider_code    VARCHAR(20)  NOT NULL DEFAULT 'LESCO',
  currency         VARCHAR(5)   NOT NULL DEFAULT 'PKR',
  billing_cycle    VARCHAR(20)  NOT NULL DEFAULT 'monthly',
  fixed_charges    DOUBLE       NOT NULL DEFAULT 0,
  tax_percent      DOUBLE       NOT NULL DEFAULT 17,
  fuel_adjustment  DOUBLE       NOT NULL DEFAULT 0,
  custom_slabs     LONGTEXT     NULL,
  meta             LONGTEXT     NULL,
  monthly_budget_rs DOUBLE      NULL DEFAULT 0,
  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_billing_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_maintenance_log (
  id             VARCHAR(36)  NOT NULL,
  user_id        VARCHAR(36)  NOT NULL,
  device_id      VARCHAR(36)  NOT NULL,
  type           VARCHAR(50)  NOT NULL DEFAULT 'inspection',
  technician     VARCHAR(255) NULL,
  cost_rs        DOUBLE       NULL DEFAULT 0,
  notes          TEXT         NULL,
  serviced_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  next_service_at DATETIME    NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_claim_codes (
  id           VARCHAR(36)  NOT NULL,
  user_id      VARCHAR(36)  NOT NULL,
  code         VARCHAR(20)  NOT NULL,
  status       VARCHAR(20)  NOT NULL DEFAULT 'pending',
  device_name  VARCHAR(255) NOT NULL,
  device_type  VARCHAR(50)  NOT NULL DEFAULT 'light',
  rated_power_w INT         NOT NULL DEFAULT 60,
  building_id  VARCHAR(36)  NULL,
  venue_id     VARCHAR(36)  NULL,
  room_id      VARCHAR(36)  NULL,
  device_id    VARCHAR(36)  NULL,
  expires_at   DATETIME     NOT NULL,
  claimed_at   DATETIME     NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_settings (
  id           VARCHAR(36)  NOT NULL,
  theme_preset VARCHAR(30)  NOT NULL DEFAULT 'yellow',
  brand_logo_url VARCHAR(500) NULL,
  updated_by   VARCHAR(36)  NULL,
  updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS partner_clients (
  id               VARCHAR(36)  NOT NULL,
  partner_user_id  VARCHAR(36)  NOT NULL,
  client_user_id   VARCHAR(36)  NOT NULL,
  client_name      VARCHAR(255) NOT NULL,
  status           VARCHAR(20)  NOT NULL DEFAULT 'active',
  revenue_share_pct DOUBLE      NOT NULL DEFAULT 20,
  notes            TEXT         NULL,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license (
  id              TINYINT      NOT NULL DEFAULT 1,
  activated_at    DATETIME     NOT NULL,
  expires_at      DATETIME     NOT NULL,
  duration_days   INT          NOT NULL,
  activated_by    VARCHAR(255) NULL,
  hard_locked     TINYINT      NOT NULL DEFAULT 0,
  failed_attempts INT          NOT NULL DEFAULT 0,
  last_attempt_at DATETIME     NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license_attempts (
  id              TINYINT NOT NULL DEFAULT 1,
  failed_attempts INT     NOT NULL DEFAULT 0,
  hard_locked     TINYINT NOT NULL DEFAULT 0,
  last_attempt_at DATETIME NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO license_attempts (id, failed_attempts, hard_locked) VALUES (1, 0, 0);

CREATE TABLE IF NOT EXISTS pairing_log (
  id         INT         NOT NULL AUTO_INCREMENT,
  user_id    VARCHAR(36) NOT NULL,
  mac_address VARCHAR(20) NOT NULL,
  device_id  VARCHAR(36) NOT NULL,
  source     VARCHAR(30) NOT NULL DEFAULT 'ble',
  created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pairing_log_mac (user_id, mac_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_state (
  device_id     VARCHAR(36)  NOT NULL,
  user_id       VARCHAR(36)  NOT NULL,
  desired_json  LONGTEXT     NULL,
  reported_json LONGTEXT     NULL,
  local_ip      VARCHAR(50)  NULL,
  local_ip_seen DATETIME     NULL,
  last_seen     DATETIME     NULL,
  online        TINYINT      NOT NULL DEFAULT 0,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (device_id),
  KEY idx_device_state_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id         INT         NOT NULL AUTO_INCREMENT,
  user_id    VARCHAR(36) NULL,
  action     VARCHAR(100) NOT NULL,
  entity     VARCHAR(50)  NULL,
  entity_id  VARCHAR(36)  NULL,
  meta_json  LONGTEXT     NULL,
  ip         VARCHAR(50)  NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_created (created_at),
  KEY idx_audit_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS firmware_versions (
  id         VARCHAR(36)  NOT NULL,
  version    VARCHAR(50)  NOT NULL,
  url        VARCHAR(500) NOT NULL,
  sha256     VARCHAR(64)  NULL,
  notes      TEXT         NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS backups (
  id         INT      NOT NULL AUTO_INCREMENT,
  path       TEXT     NOT NULL,
  size_bytes INT      NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_calibration (
  device_id      VARCHAR(36)  NOT NULL,
  user_id        VARCHAR(36)  NOT NULL,
  voltage        DOUBLE       NOT NULL DEFAULT 230.0,
  current_factor DOUBLE       NOT NULL DEFAULT 1.0,
  current_offset DOUBLE       NOT NULL DEFAULT 0.0,
  rated_amps     DOUBLE       NULL,
  notes          TEXT         NULL,
  calibrated_at  DATETIME     NULL,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (device_id),
  CONSTRAINT fk_calibration_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ac_telemetry (
  id             INT         NOT NULL AUTO_INCREMENT,
  device_id      VARCHAR(36) NOT NULL,
  user_id        VARCHAR(36) NOT NULL,
  current_amps   DOUBLE      NOT NULL,
  current_ratio  DOUBLE      NULL,
  temp_condenser DOUBLE      NULL,
  temp_inlet     DOUBLE      NULL,
  temp_outlet    DOUBLE      NULL,
  delta_temp     DOUBLE      NULL,
  humidity       DOUBLE      NULL,
  predicted_fault VARCHAR(50) NOT NULL DEFAULT 'Unknown',
  confidence     DOUBLE      NOT NULL DEFAULT 0,
  severity       VARCHAR(20) NOT NULL DEFAULT 'none',
  probabilities  LONGTEXT    NULL,
  alpha_temp     DOUBLE      NULL,
  beta_temp      DOUBLE      NULL,
  cr_physics     DOUBLE      NULL,
  data_mode      VARCHAR(30) NULL,
  recorded_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ac_tel_device (device_id, recorded_at),
  KEY idx_ac_tel_user (user_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Device commands queue (ESP32 polls for pending commands)
CREATE TABLE IF NOT EXISTS device_commands (
  id          VARCHAR(36)  NOT NULL,
  user_id     VARCHAR(36)  NOT NULL,
  device_id   VARCHAR(36)  NOT NULL,
  mac_address VARCHAR(20)  NOT NULL,
  type        VARCHAR(30)  NOT NULL,
  payload     LONGTEXT     NOT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  acked_at    DATETIME     NULL,
  PRIMARY KEY (id),
  KEY idx_devcmd_mac (mac_address, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
