-- Digit Motor Module tables
-- Run once against the NHANCE database.

CREATE TABLE IF NOT EXISTS motor_token (
    id             BIGINT PRIMARY KEY AUTO_INCREMENT,
    environment    VARCHAR(20) NOT NULL DEFAULT 'staging',
    access_token   TEXT NOT NULL,
    refresh_token  TEXT,
    expires_at     TIMESTAMP NOT NULL,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_motor_token_env (environment)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_quote (
    id                          BIGINT PRIMARY KEY AUTO_INCREMENT,
    enquiry_id                  VARCHAR(64) NOT NULL,
    quote_number                VARCHAR(32) DEFAULT NULL,
    application_id              VARCHAR(255) DEFAULT NULL,
    policy_holder_type          VARCHAR(20) NOT NULL DEFAULT 'INDIVIDUAL',
    insurance_product_code      VARCHAR(10) NOT NULL,
    sub_insurance_product_code  VARCHAR(10) NOT NULL DEFAULT 'PB',
    previous_insurer_code       SMALLINT DEFAULT NULL,
    previous_policy_expiry_date DATE DEFAULT NULL,
    external_policy_number      VARCHAR(32) DEFAULT NULL,
    is_ncb_transfer             TINYINT(1) DEFAULT 0,
    start_date                  DATE DEFAULT NULL,
    end_date                    DATE DEFAULT NULL,
    pincode                     VARCHAR(6) NOT NULL,
    coverage_details            JSON DEFAULT NULL,
    policyholder_details        JSON DEFAULT NULL,
    premium                     DECIMAL(12,2) DEFAULT NULL,
    idv                         DECIMAL(12,2) DEFAULT NULL,
    status                      VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    -- DRAFT -> QUOTED -> CREATED -> KYC_DONE -> PAID -> EFFECTIVE / FAILED
    created_by                  INT DEFAULT NULL,
    updated_by                  INT DEFAULT NULL,
    created_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_motor_quote_enquiry (enquiry_id),
    KEY idx_motor_quote_status (status),
    KEY idx_motor_quote_quote_number (quote_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_vehicle (
    id                              BIGINT PRIMARY KEY AUTO_INCREMENT,
    quote_id                        BIGINT NOT NULL,
    is_vehicle_new                  TINYINT(1) NOT NULL DEFAULT 0,
    vehicle_maincode                VARCHAR(30) NOT NULL,
    license_plate_number            VARCHAR(12) NOT NULL,
    vehicle_identification_number   VARCHAR(30) DEFAULT NULL,
    engine_number                   VARCHAR(30) DEFAULT NULL,
    manufacture_date                DATE NOT NULL,
    registration_date               DATE NOT NULL,
    registration_authority          VARCHAR(10) DEFAULT NULL,
    idv                             DECIMAL(12,2) DEFAULT NULL,
    default_idv                     DECIMAL(12,2) DEFAULT NULL,
    minimum_idv                     DECIMAL(12,2) DEFAULT NULL,
    maximum_idv                     DECIMAL(12,2) DEFAULT NULL,
    UNIQUE KEY uq_motor_vehicle_quote (quote_id),
    CONSTRAINT fk_motor_vehicle_quote FOREIGN KEY (quote_id) REFERENCES motor_quote(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_kyc (
    id                              BIGINT PRIMARY KEY AUTO_INCREMENT,
    quote_id                        BIGINT NOT NULL,
    kyc_id                          VARCHAR(64) DEFAULT NULL,
    kyc_verification_status         VARCHAR(20) DEFAULT NULL,
    reference_id                    VARCHAR(64) DEFAULT NULL,
    link                            VARCHAR(512) DEFAULT NULL,
    mismatch_type                   VARCHAR(40) DEFAULT NULL,
    id_verification_doc_type        VARCHAR(40) DEFAULT NULL,
    address_verification_doc_type   VARCHAR(40) DEFAULT NULL,
    mode                            CHAR(1) DEFAULT 'O',
    checked_at                      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_motor_kyc_quote (quote_id),
    CONSTRAINT fk_motor_kyc_quote FOREIGN KEY (quote_id) REFERENCES motor_quote(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_payment (
    id                    BIGINT PRIMARY KEY AUTO_INCREMENT,
    quote_id              BIGINT NOT NULL,
    application_id        VARCHAR(255) NOT NULL,
    digit_payment_id      VARCHAR(64) DEFAULT NULL,
    request_reference     VARCHAR(64) DEFAULT NULL,
    payment_mode          VARCHAR(5) DEFAULT 'EB',
    cancel_return_url     VARCHAR(512) DEFAULT NULL,
    success_return_url    VARCHAR(512) DEFAULT NULL,
    dispatcher_response   VARCHAR(512) DEFAULT NULL,
    premium               DECIMAL(12,2) DEFAULT NULL,
    payment_status        VARCHAR(20) DEFAULT NULL,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_motor_payment_quote (quote_id),
    CONSTRAINT fk_motor_payment_quote FOREIGN KEY (quote_id) REFERENCES motor_quote(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_policy (
    id                BIGINT PRIMARY KEY AUTO_INCREMENT,
    quote_id          BIGINT NOT NULL,
    policy_number     VARCHAR(32) DEFAULT NULL,
    policy_status     VARCHAR(20) DEFAULT NULL,
    schedule_path     VARCHAR(512) DEFAULT NULL,
    proposal_path     VARCHAR(512) DEFAULT NULL,
    response_code     VARCHAR(10) DEFAULT NULL,
    response_message  VARCHAR(255) DEFAULT NULL,
    updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_motor_policy_quote (quote_id),
    CONSTRAINT fk_motor_policy_quote FOREIGN KEY (quote_id) REFERENCES motor_quote(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_api_log (
    id              BIGINT PRIMARY KEY AUTO_INCREMENT,
    quote_id        BIGINT DEFAULT NULL,
    integration_id  VARCHAR(20) DEFAULT NULL,
    endpoint        VARCHAR(120) DEFAULT NULL,
    request_body    JSON DEFAULT NULL,
    response_body   JSON DEFAULT NULL,
    http_status     SMALLINT DEFAULT NULL,
    error_code      VARCHAR(10) DEFAULT NULL,
    duration_ms     INT DEFAULT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_motor_api_log_quote (quote_id),
    KEY idx_motor_api_log_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;