o2sound
/
castad-pre-v0.3
7
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
castad-pre-v0.3/castad-data/server/db.js

901 lines
36 KiB
JavaScript
Raw Permalink Blame History

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const bcrypt = require('bcrypt');
const DB_PATH = path.join(__dirname, 'database.sqlite');
const db = new sqlite3.Database(DB_PATH, (err) => {
if (err) {
console.error('데이터베이스 연결 실패:', err.message);
} else {
console.log('SQLite 데이터베이스에 연결되었습니다.');
}
});
// 테이블 초기화
db.serialize(() => {
// Users 테이블
db.run(`CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE,
password TEXT NOT NULL,
name TEXT,
phone TEXT,
role TEXT DEFAULT 'user',
approved INTEGER DEFAULT 0,
email_verified INTEGER DEFAULT 0,
verification_token TEXT,
reset_token TEXT,
reset_token_expiry DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
)`);
// 기존 테이블에 새 컬럼 추가 (이미 존재하면 무시)
db.run("ALTER TABLE users ADD COLUMN email TEXT UNIQUE", (err) => {});
db.run("ALTER TABLE users ADD COLUMN email_verified INTEGER DEFAULT 0", (err) => {});
db.run("ALTER TABLE users ADD COLUMN verification_token TEXT", (err) => {});
db.run("ALTER TABLE users ADD COLUMN reset_token TEXT", (err) => {});
db.run("ALTER TABLE users ADD COLUMN reset_token_expiry DATETIME", (err) => {});
// OAuth 컬럼 추가
db.run("ALTER TABLE users ADD COLUMN oauth_provider TEXT", (err) => {});
db.run("ALTER TABLE users ADD COLUMN oauth_provider_id TEXT", (err) => {});
db.run("ALTER TABLE users ADD COLUMN profile_image TEXT", (err) => {});
// 크레딧 컬럼 추가 (무료 플랜 기본값 10)
db.run("ALTER TABLE users ADD COLUMN credits INTEGER DEFAULT 10", (err) => {});
// 구독 플랜 컬럼 추가 (free, basic, pro, business)
db.run("ALTER TABLE users ADD COLUMN plan_type TEXT DEFAULT 'free'", (err) => {});
// 최대 펜션 수 (free/basic: 1, pro: 5, business: unlimited)
db.run("ALTER TABLE users ADD COLUMN max_pensions INTEGER DEFAULT 1", (err) => {});
// 월간 크레딧 한도 (플랜별 다름, 무료=10)
db.run("ALTER TABLE users ADD COLUMN monthly_credits INTEGER DEFAULT 10", (err) => {});
// 구독 시작일
db.run("ALTER TABLE users ADD COLUMN subscription_started_at DATETIME", (err) => {});
// 구독 만료일
db.run("ALTER TABLE users ADD COLUMN subscription_expires_at DATETIME", (err) => {});
// 사용자 경험 레벨 (beginner, intermediate, pro)
db.run("ALTER TABLE users ADD COLUMN experience_level TEXT DEFAULT 'beginner'", (err) => {});
// ============================================
// 자동 생성 설정 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS auto_generation_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
enabled INTEGER DEFAULT 0,
day_of_week INTEGER DEFAULT 1,
time_of_day TEXT DEFAULT '09:00',
pension_id INTEGER,
last_generated_at DATETIME,
next_scheduled_at DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// 자동 업로드 플래그 추가 (Pro 사용자용)
db.run("ALTER TABLE auto_generation_settings ADD COLUMN auto_youtube INTEGER DEFAULT 1", (err) => {});
db.run("ALTER TABLE auto_generation_settings ADD COLUMN auto_instagram INTEGER DEFAULT 1", (err) => {});
db.run("ALTER TABLE auto_generation_settings ADD COLUMN auto_tiktok INTEGER DEFAULT 1", (err) => {});
// ============================================
// 업로드 이력 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS upload_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
history_id INTEGER,
platform TEXT NOT NULL,
status TEXT DEFAULT 'pending',
external_id TEXT,
external_url TEXT,
error_message TEXT,
uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(history_id) REFERENCES history(id) ON DELETE CASCADE
)`);
// ============================================
// 자동 생성 작업 큐 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS generation_queue (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
status TEXT DEFAULT 'pending',
scheduled_at DATETIME,
started_at DATETIME,
completed_at DATETIME,
error_message TEXT,
result_video_path TEXT,
result_history_id INTEGER,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// 펜션/브랜드 프로필 테이블 (다중 펜션 지원)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS pension_profiles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
is_default INTEGER DEFAULT 0,
brand_name TEXT,
brand_name_en TEXT,
region TEXT,
address TEXT,
pension_types TEXT,
target_customers TEXT,
key_features TEXT,
nearby_attractions TEXT,
booking_url TEXT,
homepage_url TEXT,
kakao_channel TEXT,
instagram_handle TEXT,
languages TEXT DEFAULT 'KO',
price_range TEXT,
description TEXT,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// 펜션별 is_default 컬럼 추가 (기존 테이블용)
db.run("ALTER TABLE pension_profiles ADD COLUMN is_default INTEGER DEFAULT 0", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
// 펜션별 YouTube 플레이리스트 ID 직접 연결
db.run("ALTER TABLE pension_profiles ADD COLUMN youtube_playlist_id TEXT", (err) => {});
db.run("ALTER TABLE pension_profiles ADD COLUMN youtube_playlist_title TEXT", (err) => {});
// ============================================
// 펜션 이미지 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS pension_images (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pension_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
filename TEXT NOT NULL,
original_url TEXT,
file_path TEXT NOT NULL,
file_size INTEGER,
mime_type TEXT DEFAULT 'image/jpeg',
source TEXT DEFAULT 'crawl',
is_priority INTEGER DEFAULT 0,
used_count INTEGER DEFAULT 0,
last_used_at DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// pension_images 우선순위 컬럼 추가 (기존 테이블용)
db.run("ALTER TABLE pension_images ADD COLUMN is_priority INTEGER DEFAULT 0", (err) => {});
db.run("ALTER TABLE pension_images ADD COLUMN used_count INTEGER DEFAULT 0", (err) => {});
db.run("ALTER TABLE pension_images ADD COLUMN last_used_at DATETIME", (err) => {});
// ============================================
// 일일 자동 생성 설정 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS daily_auto_generation (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pension_id INTEGER NOT NULL UNIQUE,
user_id INTEGER NOT NULL,
enabled INTEGER DEFAULT 0,
generation_time TEXT DEFAULT '09:00',
image_mode TEXT DEFAULT 'random',
random_count INTEGER DEFAULT 10,
auto_upload_youtube INTEGER DEFAULT 1,
auto_upload_instagram INTEGER DEFAULT 0,
auto_upload_tiktok INTEGER DEFAULT 0,
last_generated_at DATETIME,
next_scheduled_at DATETIME,
consecutive_failures INTEGER DEFAULT 0,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// 자동 생성 로그 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS auto_generation_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pension_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
status TEXT DEFAULT 'pending',
video_path TEXT,
youtube_video_id TEXT,
instagram_media_id TEXT,
tiktok_video_id TEXT,
images_used TEXT,
error_message TEXT,
started_at DATETIME,
completed_at DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// YouTube 분석 데이터 캐시 테이블 (펜션별)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS youtube_analytics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pension_id INTEGER NOT NULL,
playlist_id TEXT NOT NULL,
date DATE NOT NULL,
views INTEGER DEFAULT 0,
playlist_starts INTEGER DEFAULT 0,
average_time_in_playlist REAL DEFAULT 0,
estimated_minutes_watched REAL DEFAULT 0,
subscribers_gained INTEGER DEFAULT 0,
likes INTEGER DEFAULT 0,
comments INTEGER DEFAULT 0,
shares INTEGER DEFAULT 0,
cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE CASCADE,
UNIQUE(pension_id, date)
)`);
// 펜션별 월간 요약 통계 테이블
db.run(`CREATE TABLE IF NOT EXISTS pension_monthly_stats (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pension_id INTEGER NOT NULL,
year_month TEXT NOT NULL,
total_views INTEGER DEFAULT 0,
total_videos INTEGER DEFAULT 0,
total_watch_time REAL DEFAULT 0,
avg_view_duration REAL DEFAULT 0,
top_video_id TEXT,
growth_rate REAL DEFAULT 0,
cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE CASCADE,
UNIQUE(pension_id, year_month)
)`);
// ============================================
// YouTube OAuth 토큰 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS youtube_connections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
google_user_id TEXT,
google_email TEXT,
youtube_channel_id TEXT,
youtube_channel_title TEXT,
access_token TEXT,
refresh_token TEXT,
token_expiry DATETIME,
scopes TEXT,
connected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// YouTube 업로드 설정 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS youtube_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
default_privacy TEXT DEFAULT 'private',
default_category_id TEXT DEFAULT '19',
default_tags TEXT,
default_hashtags TEXT,
auto_upload INTEGER DEFAULT 0,
upload_timing TEXT DEFAULT 'manual',
scheduled_day TEXT,
scheduled_time TEXT,
default_playlist_id TEXT,
notify_on_upload INTEGER DEFAULT 1,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// YouTube 플레이리스트 캐시 테이블 (펜션별 연결 지원)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS youtube_playlists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
playlist_id TEXT NOT NULL,
title TEXT,
item_count INTEGER DEFAULT 0,
cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
UNIQUE(user_id, playlist_id)
)`);
// 플레이리스트에 pension_id 컬럼 추가 (기존 테이블용)
db.run("ALTER TABLE youtube_playlists ADD COLUMN pension_id INTEGER", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
// ============================================
// 업로드 히스토리 테이블 (펜션별 연결 지원)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS upload_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
history_id INTEGER,
youtube_video_id TEXT,
youtube_url TEXT,
title TEXT,
privacy_status TEXT,
playlist_id TEXT,
uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
status TEXT DEFAULT 'completed',
error_message TEXT,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
FOREIGN KEY(history_id) REFERENCES history(id) ON DELETE SET NULL
)`);
// 업로드 히스토리에 pension_id 컬럼 추가 (기존 테이블용)
db.run("ALTER TABLE upload_history ADD COLUMN pension_id INTEGER", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
// ============================================
// Instagram 연결 정보 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS instagram_connections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
instagram_username TEXT NOT NULL,
encrypted_password TEXT NOT NULL,
encrypted_session TEXT,
is_active INTEGER DEFAULT 1,
last_login_at DATETIME,
two_factor_required INTEGER DEFAULT 0,
connected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// Instagram 업로드 설정 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS instagram_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
auto_upload INTEGER DEFAULT 0,
upload_as_reel INTEGER DEFAULT 1,
default_caption_template TEXT,
default_hashtags TEXT,
max_uploads_per_week INTEGER DEFAULT 1,
notify_on_upload INTEGER DEFAULT 1,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// Instagram 업로드 히스토리 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS instagram_upload_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
history_id INTEGER,
instagram_media_id TEXT,
instagram_post_code TEXT,
permalink TEXT,
caption TEXT,
upload_type TEXT DEFAULT 'reel',
status TEXT DEFAULT 'pending',
error_message TEXT,
retry_count INTEGER DEFAULT 0,
uploaded_at DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
FOREIGN KEY(history_id) REFERENCES history(id) ON DELETE SET NULL
)`);
// ============================================
// TikTok 연결 정보 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS tiktok_connections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
open_id TEXT NOT NULL,
display_name TEXT,
avatar_url TEXT,
follower_count INTEGER DEFAULT 0,
following_count INTEGER DEFAULT 0,
access_token TEXT NOT NULL,
refresh_token TEXT,
token_expiry DATETIME,
scopes TEXT,
connected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// TikTok 업로드 설정 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS tiktok_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
default_privacy TEXT DEFAULT 'SELF_ONLY',
disable_duet INTEGER DEFAULT 0,
disable_comment INTEGER DEFAULT 0,
disable_stitch INTEGER DEFAULT 0,
auto_upload INTEGER DEFAULT 0,
upload_to_inbox INTEGER DEFAULT 1,
default_hashtags TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)`);
// ============================================
// TikTok 업로드 히스토리 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS tiktok_upload_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
history_id INTEGER,
publish_id TEXT,
video_id TEXT,
title TEXT,
privacy_level TEXT DEFAULT 'SELF_ONLY',
status TEXT DEFAULT 'pending',
error_message TEXT,
uploaded_at DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
FOREIGN KEY(history_id) REFERENCES history(id) ON DELETE SET NULL
)`);
// ============================================
// 플랫폼 통합 통계 테이블 (YouTube, Instagram, TikTok)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS platform_stats (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
platform TEXT NOT NULL,
date DATE NOT NULL,
views INTEGER DEFAULT 0,
likes INTEGER DEFAULT 0,
comments INTEGER DEFAULT 0,
shares INTEGER DEFAULT 0,
followers_gained INTEGER DEFAULT 0,
impressions INTEGER DEFAULT 0,
reach INTEGER DEFAULT 0,
engagement_rate REAL DEFAULT 0,
cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
UNIQUE(user_id, pension_id, platform, date)
)`);
// ============================================
// 시스템 활동 로그 테이블 (어드민 분석용)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS activity_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
action_type TEXT NOT NULL,
action_detail TEXT,
ip_address TEXT,
user_agent TEXT,
metadata TEXT,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
)`);
// ============================================
// 시스템 통계 스냅샷 테이블 (일별 집계)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS system_stats_daily (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATE UNIQUE NOT NULL,
total_users INTEGER DEFAULT 0,
new_users INTEGER DEFAULT 0,
active_users INTEGER DEFAULT 0,
total_videos_generated INTEGER DEFAULT 0,
total_uploads INTEGER DEFAULT 0,
youtube_uploads INTEGER DEFAULT 0,
instagram_uploads INTEGER DEFAULT 0,
tiktok_uploads INTEGER DEFAULT 0,
total_credits_used INTEGER DEFAULT 0,
avg_generation_time REAL DEFAULT 0,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
)`);
// ============================================
// 사용자 에셋 테이블 (이미지, 오디오, 비디오)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS user_assets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
pension_id INTEGER,
history_id INTEGER,
asset_type TEXT NOT NULL,
source_type TEXT NOT NULL,
file_name TEXT NOT NULL,
file_path TEXT NOT NULL,
file_size INTEGER DEFAULT 0,
mime_type TEXT,
thumbnail_path TEXT,
duration REAL,
width INTEGER,
height INTEGER,
metadata TEXT,
is_deleted INTEGER DEFAULT 0,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
FOREIGN KEY(history_id) REFERENCES history(id) ON DELETE SET NULL
)`);
// 사용자별 스토리지 한도 컬럼 추가 (MB 단위, 기본 500MB)
db.run("ALTER TABLE users ADD COLUMN storage_limit INTEGER DEFAULT 500", (err) => {});
// 현재 사용 용량 (캐시)
db.run("ALTER TABLE users ADD COLUMN storage_used INTEGER DEFAULT 0", (err) => {});
// ============================================
// 크레딧 요청 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS credit_requests (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
requested_credits INTEGER DEFAULT 10,
status TEXT DEFAULT 'pending',
reason TEXT,
admin_note TEXT,
processed_by INTEGER,
processed_at DATETIME,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(processed_by) REFERENCES users(id) ON DELETE SET NULL
)`);
// ============================================
// 크레딧 히스토리 테이블 (변동 내역 추적)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS credit_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
type TEXT NOT NULL,
description TEXT,
balance_after INTEGER,
related_request_id INTEGER,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(related_request_id) REFERENCES credit_requests(id) ON DELETE SET NULL
)`);
// ============================================
// 렌더링 작업 큐 테이블 (백그라운드 처리)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS render_jobs (
id TEXT PRIMARY KEY,
user_id INTEGER NOT NULL,
pension_id INTEGER,
status TEXT DEFAULT 'pending',
progress INTEGER DEFAULT 0,
input_data TEXT,
output_path TEXT,
history_id INTEGER,
error_message TEXT,
credits_charged INTEGER DEFAULT 1,
credits_refunded INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
started_at DATETIME,
completed_at DATETIME,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(pension_id) REFERENCES pension_profiles(id) ON DELETE SET NULL,
FOREIGN KEY(history_id) REFERENCES history(id) ON DELETE SET NULL
)`);
// render_jobs 인덱스
db.run("CREATE INDEX IF NOT EXISTS idx_render_jobs_user ON render_jobs(user_id)");
db.run("CREATE INDEX IF NOT EXISTS idx_render_jobs_status ON render_jobs(status)");
db.run("CREATE INDEX IF NOT EXISTS idx_render_jobs_created ON render_jobs(created_at)");
// 기존 테이블에 business_name 컬럼 추가 (존재하지 않을 경우를 대비해 try-catch 대신 별도 실행)
// SQLite는 IF NOT EXISTS 컬럼 추가를 지원하지 않으므로, 에러를 무시하는 방식으로 처리하거나 스키마 버전을 관리해야 함.
// 여기서는 간단히 컬럼 추가 시도 후 에러 무시 패턴을 사용.
db.run("ALTER TABLE users ADD COLUMN business_name TEXT", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
db.run("ALTER TABLE history ADD COLUMN final_video_path TEXT", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
db.run("ALTER TABLE history ADD COLUMN poster_path TEXT", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
db.run("ALTER TABLE history ADD COLUMN pension_id INTEGER", (err) => {
// 이미 존재하면 에러가 발생하므로 무시
});
// History 테이블
db.run(`CREATE TABLE IF NOT EXISTS history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
business_name TEXT,
details TEXT,
final_video_path TEXT,
poster_path TEXT,
pension_id INTEGER,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id)
)`);
// ============================================
// 축제 테이블 (TourAPI 연동)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS festivals (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content_id TEXT UNIQUE NOT NULL,
content_type_id TEXT DEFAULT '15',
title TEXT NOT NULL,
overview TEXT,
addr1 TEXT,
addr2 TEXT,
zipcode TEXT,
area_code TEXT,
sigungu_code TEXT,
sido TEXT,
sigungu TEXT,
mapx REAL,
mapy REAL,
event_start_date TEXT,
event_end_date TEXT,
first_image TEXT,
first_image2 TEXT,
tel TEXT,
homepage TEXT,
place TEXT,
place_info TEXT,
play_time TEXT,
program TEXT,
use_fee TEXT,
age_limit TEXT,
sponsor1 TEXT,
sponsor1_tel TEXT,
sponsor2 TEXT,
sponsor2_tel TEXT,
sub_event TEXT,
booking_place TEXT,
is_active INTEGER DEFAULT 1,
view_count INTEGER DEFAULT 0,
last_synced_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
)`);
// 축제 인덱스
db.run("CREATE INDEX IF NOT EXISTS idx_festivals_area ON festivals(area_code, sigungu_code)");
db.run("CREATE INDEX IF NOT EXISTS idx_festivals_date ON festivals(event_start_date, event_end_date)");
db.run("CREATE INDEX IF NOT EXISTS idx_festivals_coords ON festivals(mapx, mapy)");
// ============================================
// 전국 펜션 마스터 테이블 (TourAPI 연동)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS public_pensions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source TEXT NOT NULL,
source_id TEXT,
content_id TEXT,
name TEXT NOT NULL,
name_normalized TEXT,
address TEXT,
road_address TEXT,
jibun_address TEXT,
zipcode TEXT,
sido TEXT,
sigungu TEXT,
eupmyeondong TEXT,
area_code TEXT,
sigungu_code TEXT,
mapx REAL,
mapy REAL,
tel TEXT,
homepage TEXT,
thumbnail TEXT,
images TEXT,
checkin_time TEXT,
checkout_time TEXT,
room_count INTEGER,
room_type TEXT,
facilities TEXT,
parking TEXT,
reservation_url TEXT,
pet_allowed INTEGER DEFAULT 0,
pickup_available INTEGER DEFAULT 0,
cooking_available INTEGER DEFAULT 0,
barbecue_available INTEGER DEFAULT 0,
business_status TEXT DEFAULT '영업중',
license_date TEXT,
closure_date TEXT,
is_verified INTEGER DEFAULT 0,
is_claimed INTEGER DEFAULT 0,
claimed_by INTEGER,
claimed_at TEXT,
view_count INTEGER DEFAULT 0,
last_synced_at TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (claimed_by) REFERENCES users(id)
)`);
// 펜션 인덱스
db.run("CREATE INDEX IF NOT EXISTS idx_public_pensions_sido ON public_pensions(sido)");
db.run("CREATE INDEX IF NOT EXISTS idx_public_pensions_sigungu ON public_pensions(sido, sigungu)");
db.run("CREATE INDEX IF NOT EXISTS idx_public_pensions_coords ON public_pensions(mapx, mapy)");
db.run("CREATE INDEX IF NOT EXISTS idx_public_pensions_name ON public_pensions(name_normalized)");
// ============================================
// 펜션-축제 매칭 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS pension_festival_matches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pension_id INTEGER NOT NULL,
pension_type TEXT DEFAULT 'public',
festival_id INTEGER NOT NULL,
distance_km REAL,
travel_time_min INTEGER,
match_type TEXT,
match_score INTEGER DEFAULT 0,
is_featured INTEGER DEFAULT 0,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (festival_id) REFERENCES festivals(id),
UNIQUE(pension_id, pension_type, festival_id)
)`);
// 매칭 인덱스
db.run("CREATE INDEX IF NOT EXISTS idx_matches_pension ON pension_festival_matches(pension_id, pension_type)");
db.run("CREATE INDEX IF NOT EXISTS idx_matches_festival ON pension_festival_matches(festival_id)");
// ============================================
// 지역코드 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS area_codes (
code TEXT PRIMARY KEY,
name TEXT NOT NULL,
name_short TEXT,
name_en TEXT
)`);
// 지역코드 초기 데이터
const areaCodes = [
['1', '서울특별시', '서울', 'Seoul'],
['2', '인천광역시', '인천', 'Incheon'],
['3', '대전광역시', '대전', 'Daejeon'],
['4', '대구광역시', '대구', 'Daegu'],
['5', '광주광역시', '광주', 'Gwangju'],
['6', '부산광역시', '부산', 'Busan'],
['7', '울산광역시', '울산', 'Ulsan'],
['8', '세종특별자치시', '세종', 'Sejong'],
['31', '경기도', '경기', 'Gyeonggi'],
['32', '강원특별자치도', '강원', 'Gangwon'],
['33', '충청북도', '충북', 'Chungbuk'],
['34', '충청남도', '충남', 'Chungnam'],
['35', '경상북도', '경북', 'Gyeongbuk'],
['36', '경상남도', '경남', 'Gyeongnam'],
['37', '전북특별자치도', '전북', 'Jeonbuk'],
['38', '전라남도', '전남', 'Jeonnam'],
['39', '제주특별자치도', '제주', 'Jeju'],
];
areaCodes.forEach(([code, name, nameShort, nameEn]) => {
db.run("INSERT OR IGNORE INTO area_codes (code, name, name_short, name_en) VALUES (?, ?, ?, ?)",
[code, name, nameShort, nameEn]);
});
// pension_profiles에 좌표 컬럼 추가 (기존 테이블용)
db.run("ALTER TABLE pension_profiles ADD COLUMN mapx REAL", (err) => {});
db.run("ALTER TABLE pension_profiles ADD COLUMN mapy REAL", (err) => {});
db.run("ALTER TABLE pension_profiles ADD COLUMN area_code TEXT", (err) => {});
db.run("ALTER TABLE pension_profiles ADD COLUMN sigungu_code TEXT", (err) => {});
db.run("ALTER TABLE pension_profiles ADD COLUMN public_pension_id INTEGER", (err) => {});
// ============================================
// API 사용량 추적 테이블 (Gemini, Suno 등)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS api_usage_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
service TEXT NOT NULL,
model TEXT,
endpoint TEXT,
user_id INTEGER,
tokens_input INTEGER DEFAULT 0,
tokens_output INTEGER DEFAULT 0,
image_count INTEGER DEFAULT 0,
audio_seconds REAL DEFAULT 0,
video_seconds REAL DEFAULT 0,
status TEXT DEFAULT 'success',
error_message TEXT,
latency_ms INTEGER DEFAULT 0,
cost_estimate REAL DEFAULT 0,
metadata TEXT,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
)`);
// API 사용량 인덱스
db.run("CREATE INDEX IF NOT EXISTS idx_api_usage_service ON api_usage_logs(service, createdAt)");
db.run("CREATE INDEX IF NOT EXISTS idx_api_usage_user ON api_usage_logs(user_id, createdAt)");
db.run("CREATE INDEX IF NOT EXISTS idx_api_usage_date ON api_usage_logs(createdAt)");
// ============================================
// 시스템 설정 테이블 (쿠키, API 키 등)
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS system_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
setting_key TEXT UNIQUE NOT NULL,
setting_value TEXT,
description TEXT,
is_encrypted INTEGER DEFAULT 0,
updated_by INTEGER,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
)`);
// ============================================
// API 일별 집계 테이블
// ============================================
db.run(`CREATE TABLE IF NOT EXISTS api_usage_daily (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATE NOT NULL,
service TEXT NOT NULL,
model TEXT,
total_calls INTEGER DEFAULT 0,
success_count INTEGER DEFAULT 0,
error_count INTEGER DEFAULT 0,
total_tokens_input INTEGER DEFAULT 0,
total_tokens_output INTEGER DEFAULT 0,
total_images INTEGER DEFAULT 0,
total_audio_seconds REAL DEFAULT 0,
total_video_seconds REAL DEFAULT 0,
total_cost_estimate REAL DEFAULT 0,
avg_latency_ms REAL DEFAULT 0,
createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(date, service, model)
)`);
// 기본 관리자 계정 생성 (존재하지 않을 경우)
const adminUsername = 'admin';
const adminPassword = 'admin123'; // 초기 비밀번호
db.get("SELECT * FROM users WHERE username = ?", [adminUsername], (err, row) => {
if (!row) {
const salt = bcrypt.genSaltSync(10);
const hash = bcrypt.hashSync(adminPassword, salt);
db.run(`INSERT INTO users (username, password, name, phone, role, approved, credits)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
[adminUsername, hash, 'Super Admin', '000-0000-0000', 'admin', 1, 999999],
(err) => {
if (err) console.error("초기 관리자 생성 실패:", err);
else console.log(`초기 관리자 계정 생성 완료. (ID: ${adminUsername}, PW: ${adminPassword})`);
});
} else if (row.role === 'admin' && (row.credits === null || row.credits < 999999)) {
// 기존 관리자에게 무제한 크레딧 부여
db.run("UPDATE users SET credits = 999999 WHERE id = ?", [row.id]);
}
});
});
module.exports = db;
Reference in New Issue View Git Blame Copy Permalink