결제 관련 db schema

create table invoice ( invoice_id int auto_increment primary key, useridx int not null, payment_id varchar(36) not null, amount varchar(100) null, payment_info json null, order_id varchar(255) null, response json null, created_at datetime default CURRENT_TIMESTAMP null, constraint invoice_ibfk_1 foreign key (useridx) references user (useridx), constraint invoice_ibfk_2 foreign key (payment_id) references payment (payment_id) ); create index payment_id on invoice (payment_id); create index useridx on invoice (useridx);

create table payment ( payment_id varchar(36) not null primary key, useridx int not null, plan_id int null, plan_duration enum ('monthly', 'yearly') null, currency varchar(10) default 'KRW' null, status enum ('pending', 'completed', 'failed', 'refunded') not null, method json null, transaction_id varchar(255) null, created_at datetime default CURRENT_TIMESTAMP null, constraint transaction_id unique (transaction_id), constraint payment_ibfk_1 foreign key (useridx) references user (useridx) ); create index useridx on payment (useridx);

create table payment_history ( id int auto_increment primary key, useridx int not null, billing_key varchar(255) not null comment 'PortOne 빌링키', payment_id varchar(255) not null comment 'PortOne 결제 ID', payment_info_id int null, amount decimal(10, 2) not null comment '결제 금액', currency varchar(10) default 'KRW' not null comment '결제 통화', status enum ('paid', 'failed', 'canceled') not null comment '결제 상태', paid_at datetime not null comment '결제 완료 시간', pgtxId varchar(200) null, created_at datetime default CURRENT_TIMESTAMP null comment '생성 시간', updated_at datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '업데이트 시간', constraint payment_history_ibfk_1 foreign key (useridx) references user (useridx) ); create index payment_history_ibfk_2 on payment_history (billing_key);

create table payment_info ( payment_info_id int auto_increment primary key, useridx int not null, name varchar(100) not null, company varchar(100) not null, email varchar(100) not null, phone varchar(20) not null, plan_duration enum ('monthly', 'yearly') not null, created_at datetime default CURRENT_TIMESTAMP null, updated_at datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, plan_id int null, constraint payment_info_ibfk_1 foreign key (useridx) references user (useridx), constraint payment_info_ibfk_2 foreign key (plan_id) references plan (id) ); create index useridx_idx on payment_info (useridx);

create table payment_sessions ( session_id varchar(36) not null primary key, useridx int not null, session_key varchar(36) not null, expires_at datetime not null, created_at datetime default CURRENT_TIMESTAMP null, updated_at datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, is_active tinyint(1) default 1 not null, payment_info_id int null, constraint payment_sessions_ibfk_1 foreign key (useridx) references user (useridx) ); create index session_key_idx on payment_sessions (session_key); create index useridx_idx on payment_sessions (useridx);

create table plan ( id int auto_increment primary key, plan_type enum ('basic', 'pro', 'enterprise', 'free') not null, price json null, survey_limit int not null, question_limit int null, translation int null, question_type json null, theme int null, insight int null, overview_result int null, idi int null, team_limit int null, constraint plan_type unique (plan_type), constraint plan_type_2 unique (plan_type) );

create table user_plan ( useridx int not null primary key, plan_type enum ('basic', 'pro', 'enterprise', 'free') not null, billing_key varchar(255) null comment 'PortOne 빌링키', payment_info_id int null, start_date datetime not null, end_date datetime null, payment_id varchar(255) null comment 'PortOne 결제 ID', is_active tinyint(1) default 1 null, created_at datetime default CURRENT_TIMESTAMP null, updated_at datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP, locale varchar(10) null comment '사용자 언어 코드', payment_status enum ('pending', 'paid', 'failed', 'cancelled') default 'pending' not null comment '결제 상태', last_notified_at datetime null comment '마지막 알림 전송 시간', constraint user_plan_ibfk_1 foreign key (plan_type) references plan (plan_type), constraint user_plan_ibfk_2 foreign key (useridx) references user (useridx), constraint user_plan_ibfk_3 foreign key (payment_info_id) references payment_info (payment_info_id) ); create index idx_billing_key on user_plan (billing_key); create index plan_type on user_plan (plan_type);

create table user_info ( useridx int not null primary key, nickname varchar(100) null, email varchar(100) null, platform varchar(50) null, agentinfo json null, devicetype varchar(20) null, ip_address varchar(50) null, photourl varchar(200) null, locale varchar(50) null, constraint userid unique (useridx) ) charset = utf8mb3;