CREATE DATABASE IF NOT EXISTS appbuilder
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE appbuilder;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS release_logs;
DROP TABLE IF EXISTS release_jobs;
DROP TABLE IF EXISTS store_connections;
DROP TABLE IF EXISTS build_artifacts;
DROP TABLE IF EXISTS build_logs;
DROP TABLE IF EXISTS build_jobs;
DROP TABLE IF EXISTS app_versions;
DROP TABLE IF EXISTS apps;
DROP TABLE IF EXISTS payment_webhook_events;
DROP TABLE IF EXISTS payment_transactions;
DROP TABLE IF EXISTS organization_subscriptions;
DROP TABLE IF EXISTS billing_plans;
DROP TABLE IF EXISTS login_attempts;
DROP TABLE IF EXISTS organization_members;
DROP TABLE IF EXISTS organizations;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  status ENUM('active','blocked','pending') NOT NULL DEFAULT 'active',
  last_login_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),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE organizations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL,
  plan_code VARCHAR(50) NOT NULL DEFAULT 'starter',
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  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_organizations_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE billing_plans (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  monthly_price INT UNSIGNED NOT NULL DEFAULT 0,
  app_limit SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0이면 무제한',
  monthly_build_limit INT UNSIGNED NOT NULL DEFAULT 10 COMMENT '0이면 무제한',
  storage_gb SMALLINT UNSIGNED NOT NULL DEFAULT 5,
  features_json JSON NULL,
  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 (code)
) ENGINE=InnoDB;

CREATE TABLE organization_members (
  organization_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role ENUM('owner','admin','developer','viewer') NOT NULL DEFAULT 'viewer',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (organization_id, user_id),
  CONSTRAINT fk_members_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  CONSTRAINT fk_members_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE organization_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  plan_code VARCHAR(50) NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'manual',
  provider_customer_id VARCHAR(190) NULL,
  provider_subscription_id VARCHAR(190) NULL,
  status ENUM('trialing','active','past_due','cancelled','expired') NOT NULL DEFAULT 'active',
  current_period_start DATETIME NULL,
  current_period_end DATETIME NULL,
  cancel_at_period_end TINYINT(1) 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_subscription_org (organization_id),
  KEY idx_subscription_provider (provider, provider_subscription_id),
  CONSTRAINT fk_subscription_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  CONSTRAINT fk_subscription_plan FOREIGN KEY (plan_code) REFERENCES billing_plans(code)
) ENGINE=InnoDB;

CREATE TABLE payment_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NULL,
  provider VARCHAR(40) NOT NULL,
  provider_payment_id VARCHAR(190) NULL,
  merchant_uid VARCHAR(120) NOT NULL,
  amount INT UNSIGNED NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'KRW',
  status ENUM('pending','paid','failed','cancelled','refunded','partial_refunded') NOT NULL DEFAULT 'pending',
  paid_at DATETIME NULL,
  refunded_at DATETIME NULL,
  response_json JSON 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_payment_merchant_uid (merchant_uid),
  UNIQUE KEY uq_payment_provider_id (provider, provider_payment_id),
  KEY idx_payment_org_time (organization_id, created_at),
  CONSTRAINT fk_payment_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
  CONSTRAINT fk_payment_subscription FOREIGN KEY (subscription_id) REFERENCES organization_subscriptions(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE payment_webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider VARCHAR(40) NOT NULL,
  event_id VARCHAR(190) NOT NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  payload_json JSON NOT NULL,
  status ENUM('received','processed','failed','ignored') NOT NULL DEFAULT 'received',
  error_message TEXT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payment_webhook_event (provider, event_id),
  KEY idx_payment_webhook_status (status, received_at)
) ENGINE=InnoDB;

CREATE TABLE login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_login_attempts_email_time (email, attempted_at),
  KEY idx_login_attempts_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB;

CREATE TABLE apps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL,
  package_name VARCHAR(190) NOT NULL,
  bundle_id VARCHAR(190) NOT NULL,
  description VARCHAR(500) NULL,
  platforms SET('android','ios') NOT NULL DEFAULT 'android,ios',
  status ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
  definition_json JSON NOT NULL,
  current_version_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED 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_apps_org_slug (organization_id, slug),
  UNIQUE KEY uq_apps_package (package_name),
  UNIQUE KEY uq_apps_bundle (bundle_id),
  KEY idx_apps_org_status (organization_id, status),
  CONSTRAINT fk_apps_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
  CONSTRAINT fk_apps_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE app_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  app_id BIGINT UNSIGNED NOT NULL,
  version_name VARCHAR(30) NOT NULL,
  android_version_code INT UNSIGNED NOT NULL,
  ios_build_number INT UNSIGNED NOT NULL,
  environment ENUM('development','staging','production') NOT NULL DEFAULT 'production',
  template_version VARCHAR(30) NOT NULL,
  schema_version VARCHAR(30) NOT NULL,
  definition_json JSON NOT NULL,
  definition_hash CHAR(64) NOT NULL,
  release_notes TEXT NULL,
  status ENUM('draft','building','ready','released','failed') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_app_version_platform_numbers (app_id, android_version_code, ios_build_number),
  KEY idx_app_versions_app_created (app_id, created_at),
  CONSTRAINT fk_versions_app FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE,
  CONSTRAINT fk_versions_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

ALTER TABLE apps
  ADD CONSTRAINT fk_apps_current_version FOREIGN KEY (current_version_id) REFERENCES app_versions(id) ON DELETE SET NULL;

CREATE TABLE build_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  app_id BIGINT UNSIGNED NOT NULL,
  app_version_id BIGINT UNSIGNED NOT NULL,
  platform ENUM('android','ios') NOT NULL,
  environment ENUM('development','staging','production') NOT NULL,
  status ENUM('queued','running','succeeded','failed','cancel_requested','cancelled','retry_wait') NOT NULL DEFAULT 'queued',
  priority SMALLINT NOT NULL DEFAULT 0,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 2,
  available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_by VARCHAR(100) NULL,
  locked_at DATETIME NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
  current_step VARCHAR(100) NULL,
  error_code VARCHAR(80) NULL,
  error_message TEXT NULL,
  workspace_path VARCHAR(500) NULL,
  requested_by BIGINT UNSIGNED 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),
  KEY idx_build_queue (status, available_at, priority, id),
  KEY idx_build_app_created (app_id, created_at),
  CONSTRAINT fk_build_app FOREIGN KEY (app_id) REFERENCES apps(id),
  CONSTRAINT fk_build_version FOREIGN KEY (app_version_id) REFERENCES app_versions(id),
  CONSTRAINT fk_build_user FOREIGN KEY (requested_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE build_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  build_job_id BIGINT UNSIGNED NOT NULL,
  level ENUM('debug','info','warning','error') NOT NULL DEFAULT 'info',
  step VARCHAR(100) NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_build_logs_job_id (build_job_id, id),
  CONSTRAINT fk_logs_build FOREIGN KEY (build_job_id) REFERENCES build_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE build_artifacts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  build_job_id BIGINT UNSIGNED NOT NULL,
  artifact_type ENUM('aab','apk','ipa','dsym','mapping','symbols','log','manifest','other') NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(700) NOT NULL,
  mime_type VARCHAR(120) NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  sha256 CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_artifacts_build (build_job_id),
  CONSTRAINT fk_artifacts_build FOREIGN KEY (build_job_id) REFERENCES build_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE store_connections (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  app_id BIGINT UNSIGNED NULL,
  provider ENUM('google_play','app_store') NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  credential_ciphertext LONGTEXT NOT NULL,
  credential_meta JSON NULL,
  status ENUM('active','invalid','revoked') NOT NULL DEFAULT 'active',
  last_verified_at DATETIME NULL,
  created_by BIGINT UNSIGNED 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),
  KEY idx_store_org_provider (organization_id, provider),
  CONSTRAINT fk_store_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
  CONSTRAINT fk_store_app FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE,
  CONSTRAINT fk_store_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE release_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  build_job_id BIGINT UNSIGNED NOT NULL,
  store_connection_id BIGINT UNSIGNED NOT NULL,
  provider ENUM('google_play','app_store') NOT NULL,
  track VARCHAR(50) NULL,
  release_status VARCHAR(50) NULL,
  status ENUM('queued','running','submitted','succeeded','failed','cancelled') NOT NULL DEFAULT 'queued',
  external_id VARCHAR(255) NULL,
  request_json JSON NULL,
  response_json JSON NULL,
  error_message TEXT NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  locked_by VARCHAR(100) NULL,
  locked_at DATETIME NULL,
  started_at DATETIME NULL,
  finished_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),
  KEY idx_release_queue (status, id),
  CONSTRAINT fk_release_build FOREIGN KEY (build_job_id) REFERENCES build_jobs(id),
  CONSTRAINT fk_release_connection FOREIGN KEY (store_connection_id) REFERENCES store_connections(id),
  CONSTRAINT fk_release_user FOREIGN KEY (requested_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE release_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  release_job_id BIGINT UNSIGNED NOT NULL,
  level ENUM('debug','info','warning','error') NOT NULL DEFAULT 'info',
  step VARCHAR(100) NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_release_logs_job_id (release_job_id, id),
  CONSTRAINT fk_release_logs_job FOREIGN KEY (release_job_id) REFERENCES release_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  target_type VARCHAR(80) NULL,
  target_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_org_time (organization_id, created_at),
  KEY idx_audit_target (target_type, target_id),
  CONSTRAINT fk_audit_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
