create table card_company
(
    card_company_id INT NOT NULL AUTO_INCREMENT,
    card_company_name_ko VARCHAR(31),
    card_company_name_en VARCHAR(31),
    card_company_image_path VARCHAR(255),
    PRIMARY KEY(card_company_id)
) DEFAULT CHARSET=utf8;

create table card
(
    card_id INT NOT NULL AUTO_INCREMENT,
    card_company_id INT,

    selective_card BOOLEAN NOT NULL,
    credit_card BOOLEAN NOT NULL,
    check_card BOOLEAN NOT NULL,
    corporate_card BOOLEAN NOT NULL,
    premium_card BOOLEAN NOT NULL,
    corp_individual BOOLEAN NOT NULL,
    corp_communal BOOLEAN NOT NULL,

    url VARCHAR(255) NOT NULL,
    mobile_url VARCHAR(255) NOT NULL,
    image_path VARCHAR(255) NOT NULL,

    issue_url_desktop VARCHAR(255) NOT NULL,
    issue_url_mobile VARCHAR(255) NOT NULL,
    issue_phone_number VARCHAR(255) NOT NULL,

    exportable BOOLEAN NOT NULL,
    contracted BOOLEAN NOT NULL,
    stopped BOOLEAN NOT NULL,

    description TEXT,
    annual_cost_explanation TEXT,

    PRIMARY KEY(card_id),
    FOREIGN KEY(card_company_id) REFERENCES card_company(card_company_id)
) DEFAULT CHARSET=utf8;

create table card_issue_restriction
(
    card_issue_restriction_id INT NOT NULL AUTO_INCREMENT,
    description TEXT,

    PRIMARY KEY(card_issue_restriction_id)
) DEFAULT CHARSET=utf8;

create table card_issue_user_restrictions
(
    card_id INT NOT NULL,
    card_issue_restriction_id INT NOT NULL,

    PRIMARY KEY(card_id, card_issue_restriction_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id),
    FOREIGN KEY(card_issue_restriction_id) REFERENCES card_issue_restriction(card_issue_restriction_id)
) DEFAULT CHARSET=utf8;

create table card_issue
(
    card_id INT NOT NULL,

    issue_id INT NOT NULL AUTO_INCREMENT,
    issued_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    issued_means ENUM('tel', 'online') NOT NULL,
    issued_platform ENUM('app_ios', 'app_android', 'web') NOT NULL,

    gain_amount INT NOT NULL,
    gain_difference INT NOT NULL,

    PRIMARY KEY(issue_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id)
) DEFAULT CHARSET=utf8;

create table card_condition
(
    card_condition_id INT NOT NULL AUTO_INCREMENT,
    affecting_months TINYINT NOT NULL,
    card_condition_criteria VARCHAR(255) NOT NULL,

    time_unit ENUM('day', 'week', 'year', 'month', 'quarter') NOT NULL,
    unit_period INT NOT NULL,

    PRIMARY KEY(card_condition_id)
) DEFAULT CHARSET=utf8;

create table card_restriction
(
    card_restriction_id INT NOT NULL AUTO_INCREMENT,
    card_restriction_criteria VARCHAR(255) NOT NULL,

    card_restriction_domain ENUM('discount', 'point', 'mileage'),
    card_restriction_formula ENUM('absolute', 'relative'),

    restricts_additional_benefits BOOLEAN NOT NULL,

    time_unit ENUM('day', 'week', 'year', 'month', 'quarter') NOT NULL,
    unit_period INT NOT NULL,

    PRIMARY KEY(card_restriction_id)
) DEFAULT CHARSET=utf8;

create table card_condition_to_restriction
(
    card_id INT NOT NULL,
    card_condition_to_restriction_id INT NOT NULL AUTO_INCREMENT,
    propagation_priority TINYINT,
    card_condition_id INT NOT NULL,
    card_restriction_id INT NOT NULL,

    PRIMARY KEY(card_condition_to_restriction_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id),
    FOREIGN KEY(card_condition_id) REFERENCES card_condition(card_condition_id),
    FOREIGN KEY(card_restriction_id) REFERENCES card_restriction(card_restriction_id)
) DEFAULT CHARSET=utf8;

create table card_base_condition
(
    card_base_condition_id INT NOT NULL AUTO_INCREMENT,
    card_id INT NOT NULL,
    card_condition_id INT NOT NULL,

    PRIMARY KEY(card_base_condition_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id),
    FOREIGN KEY(card_condition_id) REFERENCES card_condition(card_condition_id)
) DEFAULT CHARSET=utf8;

create table card_base_restriction
(
    card_base_restriction_id INT NOT NULL AUTO_INCREMENT,
    card_id INT NOT NULL,
    card_restriction_id INT NOT NULL,

    PRIMARY KEY(card_base_restriction_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id),
    FOREIGN KEY(card_restriction_id) REFERENCES card_restriction(card_restriction_id)
) DEFAULT CHARSET=utf8;

create table card_conditional_restrictions
(
    card_id INT NOT NULL,
    condition_to_restriction_id INT NOT NULL,

    PRIMARY KEY(card_id, condition_to_restriction_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id),
    FOREIGN KEY(condition_to_restriction_id) REFERENCES card_condition_to_restriction(card_condition_to_restriction_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_branch
(
    branch_id INT NOT NULL AUTO_INCREMENT,
    card_id INT NOT NULL,
    branch_description VARCHAR(255),
    max_number_of_groups TINYINT,
    branch_type ENUM('general', 'mandatory', 'selective', 'optional') NOT NULL,

    PRIMARY KEY(branch_id),
    FOREIGN KEY(card_id) REFERENCES card(card_id),
    CHECK((branch_type = 'mandatory' and max_number_of_groups is NULL) or (branch_type = 'optional' or branch_type = 'selective'))
) DEFAULT CHARSET=utf8;

create table store
(
    store_id INT NOT NULL AUTO_INCREMENT,
    store_name VARCHAR(255),
    store_image_path VARCHAR(255),

    parent_store_id INT,
    general_store BOOLEAN NOT NULL DEFAULT FALSE,

    PRIMARY KEY(store_id),
    FOREIGN KEY(parent_store_id) REFERENCES store(store_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_group
(
    group_id INT NOT NULL AUTO_INCREMENT,
    group_description VARCHAR(255),
    additional_annual_cost INT NOT NULL,

    PRIMARY KEY(group_id)
) DEFAULT CHARSET=utf8;

create table card_benefit
(
    benefit_id INT NOT NULL AUTO_INCREMENT,
    benefit_title VARCHAR(127) DEFAULT NULL,
    benefit_description TEXT,

    convertible BOOLEAN NOT NULL,
    overspent_possible BOOLEAN NOT NULL,
    direct BOOLEAN NOT NULL,
    charged BOOLEAN NOT NULL,
    cashback BOOLEAN NOT NULL,
    over_discounted BOOLEAN NOT NULL,
    excluded BOOLEAN NOT NULL,

    card_restriction_applied BOOLEAN NOT NULL,
    general_benefit_applied BOOLEAN NOT NULL,
    general_benefit_applied_under_condition BOOLEAN NOT NULL,
    general_benefit_applied_over_restriction BOOLEAN NOT NULL,

    benefit_criteria VARCHAR(255),

    PRIMARY KEY(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_meta_gain
(
    benefit_meta_gain_id INT NOT NULL AUTO_INCREMENT,
    benefit_id INT NOT NULL,

    propagation_priority INT NOT NULL,

    PRIMARY KEY(benefit_meta_gain_id),
    FOREIGN KEY(benefit_id) REFERENCES card_benefit(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_computation_scheme
(
    computation_scheme_id INT NOT NULL AUTO_INCREMENT,
    benefit_meta_gain_id INT NOT NULL,

    computation_type ENUM('constant', 'gas_ratio', 'percentage', 'generic_ratio') NOT NULL,

    PRIMARY KEY(computation_scheme_id),
    FOREIGN KEY(benefit_meta_gain_id) REFERENCES card_benefit_meta_gain(benefit_meta_gain_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_gas_ratio
(
    gas_ratio_id INT NOT NULL AUTO_INCREMENT,
    computation_scheme_id INT NOT NULL,

    gas_type ENUM('gasoline', 'diesel', 'kerosene', 'lpg') NOT NULL,
    gain_per_liter INT NOT NULL,

    PRIMARY KEY(gas_ratio_id),
    FOREIGN KEY(computation_scheme_id) REFERENCES card_benefit_computation_scheme(computation_scheme_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_generic_ratio
(
    generic_ratio_id INT NOT NULL AUTO_INCREMENT,
    computation_scheme_id INT NOT NULL,

    denominator INT NOT NULL,
    numerator INT NOT NULL,

    PRIMARY KEY(generic_ratio_id),
    FOREIGN KEY(computation_scheme_id) REFERENCES card_benefit_computation_scheme(computation_scheme_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_percentage
(
    percentage_id INT NOT NULL AUTO_INCREMENT,
    computation_scheme_id INT NOT NULL,

    percentange DECIMAL(5, 2) NOT NULL,

    PRIMARY KEY(percentage_id),
    FOREIGN KEY(computation_scheme_id) REFERENCES card_benefit_computation_scheme(computation_scheme_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_constant
(
    constant_id INT NOT NULL AUTO_INCREMENT,
    computation_scheme_id INT NOT NULL,

    constant INT NOT NULL,

    PRIMARY KEY(constant_id),
    FOREIGN KEY(computation_scheme_id) REFERENCES card_benefit_computation_scheme(computation_scheme_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_gain_scheme
(
    gain_scheme_id INT NOT NULL AUTO_INCREMENT,
    benefit_meta_gain_id INT NOT NULL,

    gain_type ENUM('discount', 'point', 'mileage') NOT NULL,
    additional BOOLEAN NOT NULL,

    point_mileage_code VARCHAR(127),

    PRIMARY KEY(gain_scheme_id),
    FOREIGN KEY(benefit_meta_gain_id) REFERENCES card_benefit_meta_gain(benefit_meta_gain_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_condition
(
    benefit_condition_id INT NOT NULL AUTO_INCREMENT,
    benefit_id INT NOT NULL,
    affecting_months TINYINT,
    condition_criteria VARCHAR(255) NOT NULL,

    time_unit ENUM('day', 'week', 'year', 'month', 'quarter') NOT NULL,
    unit_period INT NOT NULL,

    PRIMARY KEY(benefit_condition_id),
    FOREIGN KEY(benefit_id) REFERENCES card_benefit(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_payment_condition
(
    payment_condition_id INT NOT NULL AUTO_INCREMENT,
    payment_threshold_value INT NOT NULL,
    benefit_id INT NOT NULL,
    PRIMARY KEY(payment_condition_id),
    FOREIGN KEY(benefit_id) REFERENCES card_benefit(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_condition_affecting_stores
(
    benefit_condition_id INT NOT NULL,
    store_id INT NOT NULL,
    PRIMARY KEY(benefit_condition_id, store_id),
    FOREIGN KEY(benefit_condition_id) REFERENCES card_benefit_condition(benefit_condition_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_restriction
(
    benefit_restriction_id INT NOT NULL AUTO_INCREMENT,
    benefit_id INT NOT NULL,
    restriction_criteria VARCHAR(255) NOT NULL,
    restriction_months TINYINT,
    restriction_type ENUM('amount', 'time', 'discount', 'point', 'number', 'mile') NOT NULL,
    applied_complementarily BOOLEAN NOT NULL DEFAULT FALSE,

    restricts_additional_benefits BOOLEAN NOT NULL,

    time_unit ENUM('day', 'week', 'year', 'month', 'quarter') NOT NULL,
    unit_period INT NOT NULL,

    PRIMARY KEY(benefit_restriction_id),
    FOREIGN KEY(benefit_id) REFERENCES card_benefit(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_payment_restriction
(
    payment_restriction_id INT NOT NULL AUTO_INCREMENT,
    benefit_id INT NOT NULL,
    payment_restricted_value INT NOT NULL,
    PRIMARY KEY(payment_restriction_id),
    FOREIGN KEY(benefit_id) REFERENCES card_benefit(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_restricted_benefits
(
    benefit_restriction_id INT NOT NULL,
    restricted_benefit_id INT NOT NULL,
    PRIMARY KEY(benefit_restriction_id, restricted_benefit_id),
    FOREIGN KEY(benefit_restriction_id) REFERENCES card_benefit_restriction(benefit_restriction_id),
    FOREIGN KEY(restricted_benefit_id) REFERENCES card_benefit(benefit_id)
) DEFAULT CHARSET=utf8;

create table card_restrictive_stores
(
    benefit_restriction_id INT NOT NULL,
    restrictive_store_id INT NOT NULL,
    PRIMARY KEY(benefit_restriction_id, restrictive_store_id),
    FOREIGN KEY(benefit_restriction_id) REFERENCES card_benefit_restriction(benefit_restriction_id),
    FOREIGN KEY(restrictive_store_id) REFERENCES store(store_id)
) DEFAULT CHARSET=utf8;

create table card_benefit_branch_group
(
    branch_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY(branch_id, group_id),
    FOREIGN KEY(branch_id) REFERENCES card_benefit_branch(branch_id),
    FOREIGN KEY(group_id) REFERENCES card_benefit_group(group_id)
) DEFAULT CHARSET=utf8;