-- Digit Motor master / lookup tables
-- Source: Digit API Integration kit → Masters/
-- Run once (or via: php spark digit-motor:import-masters --create-tables)

CREATE TABLE IF NOT EXISTS motor_master_vehicle (
    vehicle_code          VARCHAR(30)  NOT NULL,
    make                  VARCHAR(80)  NOT NULL,
    model                 VARCHAR(120) NOT NULL,
    variant               VARCHAR(120) DEFAULT NULL,
    body_type             VARCHAR(60)  DEFAULT NULL,
    seating_capacity      SMALLINT     DEFAULT NULL,
    power                 DECIMAL(10,2) DEFAULT NULL,
    cubic_capacity        DECIMAL(10,2) DEFAULT NULL,
    gross_vehicle_weight  DECIMAL(12,2) DEFAULT NULL,
    fuel_type             VARCHAR(40)  DEFAULT NULL,
    no_of_wheels          TINYINT      DEFAULT NULL,
    abs                   CHAR(1)      DEFAULT NULL,
    air_bags              SMALLINT     DEFAULT NULL,
    length_m              DECIMAL(10,3) DEFAULT NULL,
    ex_showroom_price     DECIMAL(14,2) DEFAULT NULL,
    price_year            SMALLINT     DEFAULT NULL,
    production_status     VARCHAR(60)  DEFAULT NULL,
    manufacturing         VARCHAR(40)  DEFAULT NULL,
    vehicle_type          VARCHAR(40)  DEFAULT NULL,
    is_active             TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (vehicle_code),
    KEY idx_mmv_make (make),
    KEY idx_mmv_make_model (make, model),
    KEY idx_mmv_make_model_variant (make, model, variant),
    KEY idx_mmv_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_previous_insurer (
    insurer_code  VARCHAR(10)  NOT NULL,
    insurer_name  VARCHAR(180) NOT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (insurer_code),
    KEY idx_mmpi_name (insurer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_product (
    product_code  VARCHAR(10)  NOT NULL,
    product_name  VARCHAR(120) NOT NULL,
    vehicle_class VARCHAR(10)  DEFAULT NULL COMMENT '2W / 4W / CV',
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_sub_product (
    id              INT PRIMARY KEY AUTO_INCREMENT,
    business_type   VARCHAR(20) NOT NULL COMMENT 'NEW / ROLLOVER',
    product_label   VARCHAR(120) NOT NULL,
    sub_product_code VARCHAR(20) NOT NULL,
    is_active       TINYINT(1)  NOT NULL DEFAULT 1,
    imported_at     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_mmsp (business_type, product_label, sub_product_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_pincode (
    pincode       VARCHAR(6)   NOT NULL,
    city          VARCHAR(120) DEFAULT NULL,
    district      VARCHAR(120) DEFAULT NULL,
    street        VARCHAR(180) DEFAULT NULL,
    taluk         VARCHAR(120) DEFAULT NULL,
    state_code    VARCHAR(10)  DEFAULT NULL,
    segment       VARCHAR(40)  DEFAULT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (pincode),
    KEY idx_mmp_city (city),
    KEY idx_mmp_state (state_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_rto (
    rto_code      VARCHAR(10)  NOT NULL,
    city_state    VARCHAR(180) DEFAULT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (rto_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_ncb (
    ncb_code      VARCHAR(30) NOT NULL,
    sort_order    SMALLINT    NOT NULL DEFAULT 0,
    is_active     TINYINT(1)  NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ncb_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_previous_policy_type (
    policy_type_code VARCHAR(20)  NOT NULL,
    description      VARCHAR(120) DEFAULT NULL,
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (policy_type_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_voluntary_deductible (
    deductible_code VARCHAR(40) NOT NULL,
    sort_order      SMALLINT    NOT NULL DEFAULT 0,
    is_active       TINYINT(1)  NOT NULL DEFAULT 1,
    imported_at     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (deductible_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_doc_type (
    doc_code      VARCHAR(10)  NOT NULL,
    doc_type      VARCHAR(60)  NOT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (doc_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_nominee_relation (
    relation_code VARCHAR(40) NOT NULL,
    is_active     TINYINT(1)  NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (relation_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_state (
    state_code    VARCHAR(10)  NOT NULL,
    state_name    VARCHAR(120) NOT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (state_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_addon_age_limit (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    addon_name    VARCHAR(120) NOT NULL,
    age_limit_4w  VARCHAR(120) DEFAULT NULL,
    age_limit_2w  VARCHAR(255) DEFAULT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_mmaal_addon (addon_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS motor_master_import_log (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    master_key    VARCHAR(60)  NOT NULL,
    source_file   VARCHAR(255) DEFAULT NULL,
    rows_upserted INT          NOT NULL DEFAULT 0,
    status        VARCHAR(20)  NOT NULL DEFAULT 'OK',
    message       TEXT         DEFAULT NULL,
    imported_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_mmil_master (master_key),
    KEY idx_mmil_imported (imported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;