데이터베이스 스키마 설계

CREATE TABLE tenants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL UNIQUE, company_name VARCHAR(200) NOT NULL, domain VARCHAR(255) DEFAULT NULL, industry VARCHAR(100) DEFAULT NULL COMMENT '업종 (음식점, 병원, 쇼핑몰 등)', plan ENUM('free','basic','pro','enterprise') DEFAULT 'free', is_active TINYINT(1) DEFAULT 1, max_agents INT DEFAULT 1 COMMENT '플랜별 최대 에이전트 수', max_conversations_per_month INT DEFAULT 100, api_key VARCHAR(64) NOT NULL UNIQUE COMMENT '위젯 연동용 API 키', settings JSON DEFAULT NULL COMMENT '테넌트별 설정 (JSON)', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_plan (plan), INDEX idx_active (is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL COMMENT 'password_hash()로 생성', name VARCHAR(100) NOT NULL, role ENUM('owner','admin','member') DEFAULT 'member', phone VARCHAR(20) DEFAULT NULL, email_verified_at TIMESTAMP NULL DEFAULT NULL, last_login_at TIMESTAMP NULL DEFAULT NULL, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE INDEX idx_email (email), INDEX idx_tenant (tenant_id), INDEX idx_role (tenant_id, role), CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscriptions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, plan ENUM('free','basic','pro','enterprise') NOT NULL, price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '월 구독료 (원)', status ENUM('active','cancelled','past_due','trialing') DEFAULT 'trialing', trial_ends_at TIMESTAMP NULL DEFAULT NULL, current_period_start TIMESTAMP NULL DEFAULT NULL, current_period_end TIMESTAMP NULL DEFAULT NULL, cancelled_at TIMESTAMP NULL DEFAULT NULL, payment_method VARCHAR(50) DEFAULT NULL COMMENT 'toss, stripe 등', external_id VARCHAR(255) DEFAULT NULL COMMENT '결제사 구독 ID', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_tenant (tenant_id), INDEX idx_status (status), INDEX idx_period_end (current_period_end), CONSTRAINT fk_subscriptions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_agents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL COMMENT '에이전트 이름 (예: 친절한 상담사)', system_prompt TEXT NOT NULL COMMENT 'AI 시스템 프롬프트', faq_data JSON DEFAULT NULL COMMENT 'FAQ 데이터 (질문-답변 쌍)', welcome_message VARCHAR(500) DEFAULT '안녕하세요! 무엇을 도와드릴까요?', model VARCHAR(50) DEFAULT 'gpt-4o-mini', temperature DECIMAL(2,1) DEFAULT 0.7, is_active TINYINT(1) DEFAULT 1, widget_color VARCHAR(7) DEFAULT '#3B82F6' COMMENT '위젯 테마 색상', total_conversations INT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_tenant (tenant_id), INDEX idx_active (tenant_id, is_active), CONSTRAINT fk_agents_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_conversations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, agent_id BIGINT UNSIGNED NOT NULL, tenant_id BIGINT UNSIGNED NOT NULL, session_id VARCHAR(64) NOT NULL COMMENT '방문자 세션 ID', visitor_name VARCHAR(100) DEFAULT NULL, visitor_email VARCHAR(255) DEFAULT NULL, messages JSON NOT NULL COMMENT '대화 내역 [{role, content, timestamp}]', message_count INT UNSIGNED DEFAULT 0, satisfaction TINYINT DEFAULT NULL COMMENT '만족도 1~5', status ENUM('active','closed','escalated') DEFAULT 'active', tokens_used INT UNSIGNED DEFAULT 0 COMMENT '사용된 AI 토큰 수', ip_address VARCHAR(45) DEFAULT NULL, user_agent VARCHAR(500) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_agent (agent_id), INDEX idx_tenant_date (tenant_id, created_at), INDEX idx_session (session_id), INDEX idx_status (tenant_id, status), CONSTRAINT fk_conversations_agent FOREIGN KEY (agent_id) REFERENCES ai_agents(id) ON DELETE CASCADE, CONSTRAINT fk_conversations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, subscription_id BIGINT UNSIGNED DEFAULT NULL, amount DECIMAL(12,2) NOT NULL COMMENT '결제 금액 (원)', currency CHAR(3) DEFAULT 'KRW', status ENUM('pending','completed','failed','refunded') DEFAULT 'pending', payment_method VARCHAR(50) NOT NULL COMMENT 'card, transfer 등', external_id VARCHAR(255) DEFAULT NULL COMMENT 'PG사 결제 ID', receipt_url VARCHAR(500) DEFAULT NULL, description VARCHAR(255) DEFAULT NULL, paid_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_tenant (tenant_id), INDEX idx_status (status), INDEX idx_paid_date (paid_at), INDEX idx_external (external_id), CONSTRAINT fk_payments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_payments_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;