DDL

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS employee_auth;
DROP TABLE IF EXISTS notification_receiver;
DROP TABLE IF EXISTS item_overdue;
DROP TABLE IF EXISTS approval_mapping;
DROP TABLE IF EXISTS login_history;
DROP TABLE IF EXISTS approval;
DROP TABLE IF EXISTS issued_coupon;
DROP TABLE IF EXISTS quote;
DROP TABLE IF EXISTS pay_overdue;
DROP TABLE IF EXISTS after_service;
DROP TABLE IF EXISTS contract;
DROP TABLE IF EXISTS contract_with_item;
DROP TABLE IF EXISTS promotion_log;
DROP TABLE IF EXISTS feedback;
DROP TABLE IF EXISTS customer_support;
DROP TABLE IF EXISTS coupon;
DROP TABLE IF EXISTS promotion;
DROP TABLE IF EXISTS survey;
DROP TABLE IF EXISTS customer_segment_history;
DROP TABLE IF EXISTS customer_risk_transition_history;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS emp_position_auth;
DROP TABLE IF EXISTS emp_position;
DROP TABLE IF EXISTS auth_list;
DROP TABLE IF EXISTS item;
DROP TABLE IF EXISTS item_category;
DROP TABLE IF EXISTS support_category;
DROP TABLE IF EXISTS survey_category;
DROP TABLE IF EXISTS segment;
DROP TABLE IF EXISTS channel;
DROP TABLE IF EXISTS payment_details;
DROP TABLE IF EXISTS notification;
DROP TABLE IF EXISTS id_sequence;
DROP TABLE IF EXISTS recommend_promotion;
DROP TABLE IF EXISTS recommend_coupon;

-- 채번용 시퀀스 테이블 (MyBatis-only 권장)
CREATE TABLE id_sequence (
                             domain VARCHAR(30) NOT NULL,   -- 예: CUS, EMP, QUO ...
                             year INT NOT NULL,             -- 예: 2025
                             last_number INT NOT NULL,
                             PRIMARY KEY (domain, year)
) ENGINE=InnoDB;

CREATE TABLE employee_auth (
                               id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                               auth_id BIGINT UNSIGNED NOT NULL,
                               emp_id  BIGINT UNSIGNED NOT NULL,
                               PRIMARY KEY (id)
);

CREATE TABLE channel (
                         id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                         name VARCHAR(255) NOT NULL,
                         PRIMARY KEY (id)
);

CREATE TABLE customer (
                          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                          customer_code VARCHAR(30) NOT NULL UNIQUE,   -- CUS-2025-001
                          name VARCHAR(255) NOT NULL,
                          in_charge VARCHAR(255) NOT NULL,
                          dept VARCHAR(255) NULL,
                          call_num VARCHAR(255) NOT NULL, -- 'call'은 예약어일 가능성이 있어 call_num 등으로 쓰는 것이 안전하나, 일단 원본 유지하려면 백틱(`) 권장. 여기선 VARCHAR 문맥상 유지합니다.
                          phone VARCHAR(255) NULL,
                          email VARCHAR(255) NOT NULL,
                          business_num VARCHAR(255) NOT NULL,
                          addr VARCHAR(255) NOT NULL,
                          last_transaction DATETIME NULL,
                          first_contract_date DATETIME NULL,
                          memo VARCHAR(2000) NULL,
                          star INTEGER NULL,
                          is_deleted VARCHAR(1) NOT NULL DEFAULT 'N',
                          channel_id BIGINT UNSIGNED NOT NULL,
                          segment_id BIGINT UNSIGNED NOT NULL,
                          PRIMARY KEY (id)
);

DROP TABLE IF EXISTS customer_churn_snapshot ;

-- index 는 빨리 찾기 위한 번호표 -> 이걸 써서 처리 속도 빨라지게 함
CREATE TABLE customer_churn_snapshot (
                                         id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '스냅샷ID',
                                         snapshot_month CHAR(7) NOT NULL COMMENT '기준월(YYYY-MM)',
                                         customer_id BIGINT UNSIGNED NOT NULL COMMENT '고객ID',
                                         is_churn_risk CHAR(1) NOT NULL COMMENT '이탈위험여부(Y/N) - N이면 즉시 복귀',
                                         risk_reason_code VARCHAR(30) NOT NULL COMMENT '이탈위험사유(EXPIRING/LOW_SAT/OVERDUE/NO_RENEWAL/NONE)',
                                         prev_segment_id BIGINT UNSIGNED NULL COMMENT '이전세그먼트ID(위험 이동 전 출처)',
                                         created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
                                         PRIMARY KEY (id),
                                         UNIQUE KEY uk_snapshot_month_customer (snapshot_month, customer_id),
                                         INDEX idx_snapshot_month (snapshot_month),
                                         INDEX idx_snapshot_risk (snapshot_month, is_churn_risk),
                                         INDEX idx_snapshot_reason (snapshot_month, risk_reason_code)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE customer_segment_history (
                                          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                          customer_id BIGINT UNSIGNED NOT NULL,
                                          previous_segment_id BIGINT UNSIGNED NULL,
                                          current_segment_id BIGINT UNSIGNED NOT NULL,
                                          reason VARCHAR(255) NULL,
                                          trigger_type VARCHAR(20) NOT NULL DEFAULT 'AUTO',
                                          reference_type VARCHAR(30) NULL,
                                          reference_id BIGINT UNSIGNED NULL,
                                          changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                          PRIMARY KEY (id)
);


DROP TABLE IF EXISTS customer_risk_transition_history;

CREATE TABLE customer_risk_transition_history (
                                                  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                                  customer_id BIGINT UNSIGNED NOT NULL,

                                                  from_segment_id BIGINT UNSIGNED NOT NULL,
                                                  to_segment_id   BIGINT UNSIGNED NOT NULL,

                                                  reason_code VARCHAR(30) NULL,
                                                  reason      VARCHAR(255) NULL,

                                                  trigger_type VARCHAR(20) NOT NULL DEFAULT 'AUTO',
                                                  reference_type VARCHAR(30) NULL,
                                                  reference_id BIGINT UNSIGNED NULL,
                                                  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                                  PRIMARY KEY (id)
);


CREATE TABLE notification_receiver (
                                       id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                       is_read VARCHAR(1) NOT NULL DEFAULT 'N',
                                       created_at DATETIME NOT NULL,
                                       read_at DATETIME NULL,
                                       noti_id BIGINT UNSIGNED NOT NULL,
                                       emp_id BIGINT UNSIGNED NOT NULL,
                                       PRIMARY KEY (id)
);

CREATE TABLE item_overdue (
                              id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                              item_overdue_code VARCHAR(30) NOT NULL UNIQUE, -- IOD-2025-001
                              count INTEGER NOT NULL,
                              due_date DATETIME NOT NULL,
                              overdue_period INTEGER NOT NULL,
                              status VARCHAR(1) NULL,
                              contract_id BIGINT UNSIGNED NULL,
                              cum_id BIGINT UNSIGNED NOT NULL,
                              PRIMARY KEY (id)
);

CREATE TABLE survey_category (
                                 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                 name VARCHAR(255) NOT NULL,
                                 PRIMARY KEY (id)
);

CREATE TABLE segment (
                         id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                         name VARCHAR(255) NOT NULL,
                         content VARCHAR(2000) NULL,
                         total_charge BIGINT NULL,
                         segment_period INTEGER NULL,
                         is_contracted BOOLEAN NULL,
                         overdued INTEGER UNSIGNED NULL,
                         PRIMARY KEY (id)
);

CREATE TABLE approval_mapping (
                                  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                  emp_id BIGINT UNSIGNED NOT NULL,
                                  approval_id BIGINT UNSIGNED NOT NULL,
                                  step INTEGER NULL,
                                  is_approved VARCHAR(1) NULL,
                                  reject_reason VARCHAR(255) NULL,
                                  PRIMARY KEY (id)
);

CREATE TABLE login_history (
                               id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                               login_success_date DATETIME NULL,
                               login_is_succeed VARCHAR(1) NOT NULL,
                               login_ip VARCHAR(255) NULL,
                               emp_id BIGINT UNSIGNED NOT NULL,
                               PRIMARY KEY (id)
);

CREATE TABLE approval (
                          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                          approval_code VARCHAR(30) NOT NULL UNIQUE, -- APP-2025-001
                          title VARCHAR(255) NOT NULL,
                          content VARCHAR(2000) NULL,
                          request_date DATETIME NOT NULL,
                          approval_date DATETIME NULL,
                          status VARCHAR(1) NOT NULL,
                          contract_id BIGINT UNSIGNED NULL,
                          emp_id BIGINT UNSIGNED NOT NULL,
                          PRIMARY KEY (id)
);

CREATE TABLE issued_coupon (
                               id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                               issued_date DATETIME NOT NULL,
                               is_used VARCHAR(1) NOT NULL,
                               used_date DATETIME NULL,
                               end_date DATETIME NULL,
                               coupon_id BIGINT UNSIGNED NOT NULL,
                               cum_id BIGINT UNSIGNED NOT NULL,
                               con_id BIGINT UNSIGNED NULL,
                               PRIMARY KEY (id)
);

CREATE TABLE survey (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        survey_code VARCHAR(30) NOT NULL UNIQUE, -- SUR-2025-001
                        name VARCHAR(255) NOT NULL,
                        link VARCHAR(255) NULL,
                        status VARCHAR(255) NULL,
                        start_date DATETIME NULL,
                        end_date DATETIME NULL,
                        ai_response MEDIUMTEXT NULL,
                        category_id BIGINT UNSIGNED NOT NULL,
                        PRIMARY KEY (id)
);

CREATE TABLE coupon (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                        coupon_code VARCHAR(30) NOT NULL UNIQUE, -- COU-2025-001
                        name VARCHAR(255) NOT NULL,
                        rate INTEGER NOT NULL,
                        content VARCHAR(255) NULL,
                        type VARCHAR(1) NOT NULL,
                        start_date DATETIME NULL,
                        end_date DATETIME NULL,
                        status VARCHAR(1) NULL,
                        date_period INTEGER NULL,
                        min_fee INTEGER NULL,
                        max_num INTEGER NULL,
                        segment_id BIGINT UNSIGNED NOT NULL,
                        PRIMARY KEY (id)
);

CREATE TABLE quote (
                       id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                       quote_code VARCHAR(30) NOT NULL UNIQUE, -- QUO-2025-001
                       counseling_date DATETIME NOT NULL,
                       counselor VARCHAR(255) NOT NULL,
                       summary VARCHAR(255) NULL,
                       content VARCHAR(2000) NULL,
                       processing_time INTEGER NOT NULL,
                       channel_id BIGINT UNSIGNED NOT NULL,
                       cum_id BIGINT UNSIGNED NOT NULL,
                       PRIMARY KEY (id)
);

CREATE TABLE emp_position (
                              id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                              position_name VARCHAR(255) NOT NULL,
                              description VARCHAR(255) NULL,
                              PRIMARY KEY (id)
);

CREATE TABLE promotion (
                           id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                           promotion_code VARCHAR(30) NOT NULL UNIQUE, -- PRO-2025-001
                           name VARCHAR(255) NOT NULL,
                           start_date DATETIME NULL,
                           end_date DATETIME NULL,
                           status VARCHAR(1) NOT NULL,
                           type VARCHAR(1) NOT NULL,
                           trigger_val VARCHAR(255) NULL,
                           content VARCHAR(255) NULL,
                           segment_id BIGINT UNSIGNED NOT NULL,
                           PRIMARY KEY (id)
);

CREATE TABLE item (
                      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                      item_code VARCHAR(30) NOT NULL UNIQUE, -- ITE-2025-001
                      name VARCHAR(255) NOT NULL,
                      monthly_price INTEGER NOT NULL,
                      status VARCHAR(1) NOT NULL,
                      last_inspect_date DATETIME NULL,
                      sales INTEGER NULL,
                      repair_cost INTEGER NULL,
                      category_id BIGINT UNSIGNED NOT NULL,
                      PRIMARY KEY (id)
);

CREATE TABLE pay_overdue (
                             id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                             pay_overdue_code VARCHAR(30) NOT NULL UNIQUE, -- POD-2025-001
                             paid_date DATETIME NULL,
                             due_date DATETIME NOT NULL,
                             overdue_period INTEGER NOT NULL,
                             status VARCHAR(1) NULL,
                             contract_id BIGINT UNSIGNED NULL,
                             cum_id BIGINT UNSIGNED NOT NULL,
                             PRIMARY KEY (id)
);

CREATE TABLE employee (
                          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                          employee_code VARCHAR(30) NOT NULL UNIQUE, -- EMP-2025-001
                          emp_id VARCHAR(255) NOT NULL,
                          pwd VARCHAR(255) NOT NULL,
                          name VARCHAR(255) NOT NULL,
                          phone VARCHAR(255) NOT NULL,
                          email VARCHAR(255) NULL,
                          addr VARCHAR(255) NOT NULL,
                          birthday DATETIME NOT NULL,
                          gender VARCHAR(1) NOT NULL,
                          status VARCHAR(1) NULL,
                          dept VARCHAR(255) NOT NULL,
                          hire_date DATETIME NOT NULL,
                          resign_date DATETIME NULL,
                          position_id BIGINT UNSIGNED NOT NULL,
                          PRIMARY KEY (id)
);

CREATE TABLE emp_position_auth (
                                   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                   position_id BIGINT UNSIGNED NOT NULL,
                                   auth_id BIGINT UNSIGNED NOT NULL,
                                   PRIMARY KEY (id)
);

CREATE TABLE after_service (
                               id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                               after_service_code VARCHAR(30) NOT NULL UNIQUE, -- AFT-2025-001
                               engineer VARCHAR(255) NOT NULL,
                               type VARCHAR(1) NOT NULL,
                               due_date DATETIME NOT NULL,
                               status VARCHAR(1) NOT NULL,
                               contents VARCHAR(2000) NULL,
                               item_id BIGINT UNSIGNED NOT NULL,
                               cum_id BIGINT UNSIGNED NOT NULL,
                               PRIMARY KEY (id)
);

CREATE TABLE contract (
                          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                          contract_code VARCHAR(30) NOT NULL UNIQUE, -- CON-2025-001
                          name VARCHAR(255) NOT NULL,
                          start_date DATETIME NOT NULL,
                          contract_period INTEGER NOT NULL,
                          status VARCHAR(1) NOT NULL,
                          monthly_payment INTEGER NULL,
                          total_amount BIGINT NOT NULL,
                          pay_method VARCHAR(1) NOT NULL,
                          special_content VARCHAR(2000) NULL,
                          current_step TINYINT NOT NULL,
                          cum_id BIGINT UNSIGNED NOT NULL,
                          is_deleted CHAR(1) NOT NULL DEFAULT 'N',
                          PRIMARY KEY (id)
);

CREATE TABLE feedback (
                          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                          feedback_code VARCHAR(30) NOT NULL UNIQUE, -- FDB-2025-001
                          title VARCHAR(255) NOT NULL,
                          content VARCHAR(2000) NOT NULL,
                          star INTEGER NULL,
                          create_date DATETIME NULL,
                          action VARCHAR(255) NULL,
                          cum_id BIGINT UNSIGNED NOT NULL,
                          category_id BIGINT UNSIGNED NOT NULL,
                          emp_id BIGINT UNSIGNED NOT NULL,
                          channel_id BIGINT UNSIGNED NOT NULL,
                          PRIMARY KEY (id)
);

CREATE TABLE support_category (
                                  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                  name VARCHAR(255) NOT NULL,
                                  type VARCHAR(1) NOT NULL,
                                  PRIMARY KEY (id)
);

CREATE TABLE promotion_log (
                               id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                               participation_date DATETIME NOT NULL,
                               cum_id BIGINT UNSIGNED NOT NULL,
                               promotion_id BIGINT UNSIGNED NOT NULL,
                               PRIMARY KEY (id)
);

CREATE TABLE contract_with_item (
                                    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                    contract_id BIGINT UNSIGNED NULL,
                                    item_id BIGINT UNSIGNED NOT NULL,
                                    PRIMARY KEY (id)
);

CREATE TABLE auth_list (
                           id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                           auth VARCHAR(255) NOT NULL,
                           description VARCHAR(255) NULL,
                           PRIMARY KEY (id)
);

CREATE TABLE item_category (
                               id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                               name VARCHAR(255) NOT NULL,
                               PRIMARY KEY (id)
);

CREATE TABLE customer_support (
                                  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                  customer_support_code VARCHAR(30) NOT NULL UNIQUE, -- CSU-2025-001
                                  title VARCHAR(255) NOT NULL,
                                  content VARCHAR(2000) NOT NULL,
                                  status VARCHAR(1) NOT NULL DEFAULT 'P',
                                  create_date DATETIME NULL,
                                  action VARCHAR(255) NULL,
                                  cum_id BIGINT UNSIGNED NOT NULL,
                                  emp_id BIGINT UNSIGNED NOT NULL,
                                  category_id BIGINT UNSIGNED NOT NULL,
                                  channel_id BIGINT UNSIGNED NOT NULL,
                                  PRIMARY KEY (id)
);

CREATE TABLE notification (
                              id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                              type VARCHAR(50) NULL,
                              title VARCHAR(255) NULL,
                              message VARCHAR(2000) NULL,
                              link_url VARCHAR(255) NULL,
                              PRIMARY KEY (id)
);

CREATE TABLE payment_details (
                                 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                 payment_due DATETIME NOT NULL,
                                 payment_actual DATETIME NULL,
                                 overdue_days INTEGER NULL,
                                 payment_status VARCHAR(1) NOT NULL,
                                 con_id BIGINT UNSIGNED NULL,
                                 PRIMARY KEY (id)
);

CREATE TABLE recommend_promotion (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    content VARCHAR(255) NULL,
    segment_name VARCHAR(255) NOT NULL,
    survey_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    is_used VARCHAR(1) NOT NULL DEFAULT 'N'
    );
    
    
CREATE TABLE recommend_coupon (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    rate INTEGER NOT NULL,
    content VARCHAR(255) NULL,
    segment_name VARCHAR(255) NOT NULL,
    survey_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    is_used VARCHAR(1) NOT NULL DEFAULT 'N' 
    );

-- 외래키 설정

ALTER TABLE employee_auth
    ADD CONSTRAINT FK_auth_list_TO_employee_auth FOREIGN KEY (auth_id) REFERENCES auth_list (id),
    ADD CONSTRAINT FK_employee_TO_employee_auth FOREIGN KEY (emp_id) REFERENCES employee (id);

ALTER TABLE customer
    ADD CONSTRAINT FK_channel_TO_customer FOREIGN KEY (channel_id) REFERENCES channel (id),
    ADD CONSTRAINT FK_segment_TO_customer FOREIGN KEY (segment_id) REFERENCES segment (id);

ALTER TABLE customer_churn_snapshot
    ADD CONSTRAINT FK_customer_TO_customer_churn_snapshot FOREIGN KEY (customer_id) REFERENCES customer (id),
    ADD CONSTRAINT FK_segment_TO_customer_churn_snapshot_prev FOREIGN KEY (prev_segment_id) REFERENCES segment (id);

ALTER TABLE customer_segment_history
    ADD CONSTRAINT FK_csh_customer FOREIGN KEY (customer_id) REFERENCES customer (id),
    ADD CONSTRAINT FK_csh_prev_segment FOREIGN KEY (previous_segment_id) REFERENCES segment (id),
    ADD CONSTRAINT FK_csh_curr_segment FOREIGN KEY (current_segment_id) REFERENCES segment (id);

ALTER TABLE customer_risk_transition_history
    ADD CONSTRAINT chk_to_segment_risk_blacklist CHECK (to_segment_id IN (4, 6));

ALTER TABLE notification_receiver
    ADD CONSTRAINT FK_notification_TO_notification_receiver FOREIGN KEY (noti_id) REFERENCES notification (id),
    ADD CONSTRAINT FK_employee_TO_notification_receiver FOREIGN KEY (emp_id) REFERENCES employee (id);

ALTER TABLE item_overdue
    ADD CONSTRAINT FK_contract_TO_item_overdue FOREIGN KEY (contract_id) REFERENCES contract (id),
    ADD CONSTRAINT FK_customer_TO_item_overdue FOREIGN KEY (cum_id) REFERENCES customer (id);

ALTER TABLE approval_mapping
    ADD CONSTRAINT FK_employee_TO_approval_mapping FOREIGN KEY (emp_id) REFERENCES employee (id),
    ADD CONSTRAINT FK_approval_TO_approval_mapping FOREIGN KEY (approval_id) REFERENCES approval (id);

ALTER TABLE login_history
    ADD CONSTRAINT FK_employee_TO_login_history FOREIGN KEY (emp_id) REFERENCES employee (id);

ALTER TABLE approval
    ADD CONSTRAINT FK_contract_TO_approval FOREIGN KEY (contract_id) REFERENCES contract (id),
    ADD CONSTRAINT FK_employee_TO_approval FOREIGN KEY (emp_id) REFERENCES employee (id);

ALTER TABLE issued_coupon
    ADD CONSTRAINT FK_coupon_TO_issued_coupon FOREIGN KEY (coupon_id) REFERENCES coupon (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_customer_TO_issued_coupon FOREIGN KEY (cum_id) REFERENCES customer (id),
    ADD CONSTRAINT FK_contract_TO_issued_coupon FOREIGN KEY (con_id) REFERENCES contract (id);

ALTER TABLE survey
    ADD CONSTRAINT FK_survey_category_TO_survey FOREIGN KEY (category_id) REFERENCES survey_category (id);

ALTER TABLE coupon
    ADD CONSTRAINT FK_segment_TO_coupon FOREIGN KEY (segment_id) REFERENCES segment (id);

ALTER TABLE quote
    ADD CONSTRAINT FK_channel_TO_quote FOREIGN KEY (channel_id) REFERENCES channel (id),
    ADD CONSTRAINT FK_customer_TO_quote FOREIGN KEY (cum_id) REFERENCES customer (id);

ALTER TABLE promotion
    ADD CONSTRAINT FK_segment_TO_promotion FOREIGN KEY (segment_id) REFERENCES segment (id);

ALTER TABLE item
    ADD CONSTRAINT FK_item_category_TO_item FOREIGN KEY (category_id) REFERENCES item_category (id);

ALTER TABLE pay_overdue
    ADD CONSTRAINT FK_contract_TO_pay_overdue FOREIGN KEY (contract_id) REFERENCES contract (id),
    ADD CONSTRAINT FK_customer_TO_pay_overdue FOREIGN KEY (cum_id) REFERENCES customer (id);

ALTER TABLE employee
    ADD CONSTRAINT FK_emp_position_TO_employee FOREIGN KEY (position_id) REFERENCES emp_position (id);

ALTER TABLE emp_position_auth
    ADD CONSTRAINT FK_emp_position_TO_emp_position_auth FOREIGN KEY (position_id) REFERENCES emp_position (id),
    ADD CONSTRAINT FK_auth_list_TO_emp_position_auth FOREIGN KEY (auth_id) REFERENCES auth_list (id);

ALTER TABLE after_service
    ADD CONSTRAINT FK_item_TO_after_service FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE,
    ADD CONSTRAINT FK_customer_TO_after_service FOREIGN KEY (cum_id) REFERENCES customer (id);

ALTER TABLE contract
    ADD CONSTRAINT FK_customer_TO_contract FOREIGN KEY (cum_id) REFERENCES customer (id);

ALTER TABLE feedback
    ADD CONSTRAINT FK_customer_TO_feedback FOREIGN KEY (cum_id) REFERENCES customer (id),
    ADD CONSTRAINT FK_support_category_TO_feedback FOREIGN KEY (category_id) REFERENCES support_category (id),
    ADD CONSTRAINT FK_employee_TO_feedback FOREIGN KEY (emp_id) REFERENCES employee (id),
    ADD CONSTRAINT FK_channel_TO_feedback FOREIGN KEY (channel_id) REFERENCES channel (id);

ALTER TABLE promotion_log
    ADD CONSTRAINT FK_customer_TO_promotion_log FOREIGN KEY (cum_id) REFERENCES customer (id),
    ADD CONSTRAINT FK_promotion_TO_promotion_log FOREIGN KEY (promotion_id) REFERENCES promotion (id) ON DELETE CASCADE;

ALTER TABLE contract_with_item
    ADD CONSTRAINT FK_contract_TO_contract_with_item FOREIGN KEY (contract_id) REFERENCES contract (id),
    ADD CONSTRAINT FK_item_TO_contract_with_item FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE;

ALTER TABLE customer_support
    ADD CONSTRAINT FK_customer_TO_customer_support FOREIGN KEY (cum_id) REFERENCES customer (id),
    ADD CONSTRAINT FK_employee_TO_customer_support FOREIGN KEY (emp_id) REFERENCES employee (id),
    ADD CONSTRAINT FK_support_category_TO_customer_support FOREIGN KEY (category_id) REFERENCES support_category (id),
    ADD CONSTRAINT FK_channel_TO_customer_support FOREIGN KEY (channel_id) REFERENCES channel (id);

ALTER TABLE payment_details
    ADD CONSTRAINT FK_con_id_TO_contract FOREIGN KEY (con_id) REFERENCES contract (id);
    
ALTER TABLE recommend_promotion
    ADD CONSTRAINT FK_survey_TO_recommend_promotion FOREIGN KEY (survey_id) REFERENCES survey (id);
    
ALTER TABLE recommend_coupon
    ADD CONSTRAINT FK_survey_TO_recommend_coupon FOREIGN KEY (survey_id) REFERENCES survey (id);


ALTER TABLE coupon
    ADD CONSTRAINT chk_coupon_exclusive_period
        CHECK (
            (
                start_date IS NOT NULL
                    AND end_date IS NOT NULL
                    AND date_period IS NULL
                )
                OR
            (
                date_period IS NOT NULL
                    AND start_date IS NULL
                    AND end_date IS NULL
                )
            );


ALTER TABLE promotion
    ADD CONSTRAINT chk_promotion_type_columns
        CHECK (
            (
                -- type = 'A' 이면 startDate, endDate 는 반드시 NULL
                type = 'A'
                    AND start_date IS NULL
                    AND end_date IS NULL
                )
                OR
            (
                -- type = 'M' 이면 trigger_val 은 반드시 NULL
                type = 'M'
                    AND trigger_val IS NULL
                )
            );

DROP VIEW IF EXISTS stocked_item;
-- view에서는 id에 auto_increment를 적용하는 것이 불가능, 피그마에서의 자산ID는 넣을거라면 프론트에서 처리해야 할 듯
CREATE VIEW stocked_item AS
SELECT
    A.name AS ITEM_NAME,
    B.name AS CATEGORY_NAME,
    A.monthly_price,
    count(*) AS ITEM_COUNT,
    A.status,
    SUM(A.sales) AS WHOLE_SALES,
    SUM(A.repair_cost) AS WHOLE_REPAIR_COST
FROM item A
         LEFT JOIN item_category B ON A.category_id = B.id
GROUP BY A.name, A.status;

DROP VIEW IF EXISTS v_approval_payment_manage;

-- 결재관리에 사용할 view
CREATE OR REPLACE VIEW v_approval_payment_manage AS
SELECT
--     approval_mapping
am.id                 AS approval_mapping_id,
am.emp_id             AS approver_emp_id,
am.step               AS approval_step,
am.is_approved        AS is_approved,
am.reject_reason      AS reject_reason,

--     approval
a.id                  AS approval_id,
a.approval_code       AS approval_code,
a.title               AS approval_title,
a.request_date        AS request_date,
a.approval_date       AS approval_date,
a.status              AS approval_status,
a.emp_id              AS request_emp_id,
a.contract_id         AS contract_id,

--     contract
c.name                AS contract_name,
c.status              AS contract_status,
c.total_amount        AS total_amount,

--     요청자 정보
e.employee_code       AS employee_code,
e.name                AS employee_name,
p.position_name       AS position_name,

--     승인자 정보 (다음 승인자 조회용)
ae.name               AS approver_name,
ap.position_name      AS approver_position_name

FROM approval_mapping am
         JOIN approval a
              ON am.approval_id = a.id
         LEFT JOIN contract c
                   ON a.contract_id = c.id

-- 요청자
         LEFT JOIN employee e
                   ON a.emp_id = e.id
         LEFT JOIN emp_position p
                   ON e.position_id = p.id

-- 승인자
         LEFT JOIN employee ae
                   ON am.emp_id = ae.id
         LEFT JOIN emp_position ap
                   ON ae.position_id = ap.id;
SET FOREIGN_KEY_CHECKS = 1;

---