CREATE DATABASE IF NOT EXISTS mta27_applications CHARACTER SET utf8mb4;
USE mta27_applications;

CREATE TABLE applications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Page 1 : fields 1-6
    name_initials       VARCHAR(60)  NOT NULL,   -- split across name_initials_1/2 (30 chars each) at print time
    full_name            VARCHAR(60)  NOT NULL,
    title                 TINYINT      NOT NULL,   -- 1 Mr, 2 Ms, 3 Other
    title_other_specify   VARCHAR(4),
    id_type               TINYINT      NOT NULL,   -- 1-5
    identity_card_no      VARCHAR(10)  NOT NULL,
    address                VARCHAR(60)  NOT NULL,

    -- Page 2 : fields 7-20
    province               VARCHAR(14),
    licensing_authority    VARCHAR(14),
    district                VARCHAR(14),
    ownership               TINYINT,               -- 1 Private, 2 Public
    usage_type               TINYINT,               -- 1 Personal, 2 Commercial
    purpose                  TINYINT,               -- 1-6
    purpose_other_specify    VARCHAR(14),
    registration_status       TINYINT,               -- 1-5
    registration_status_other_specify VARCHAR(14),
    dpl_vehicle                 ENUM('yes','no'),
    vehicle_type_code            VARCHAR(2),
    vehicle_type_other_specify   VARCHAR(14),
    make                          VARCHAR(22),
    model_body_type               VARCHAR(22),
    year_of_manufacture            VARCHAR(5),
    country_of_origin               VARCHAR(11),
    prototype_number                  VARCHAR(11),

    -- Page 3 : fields 21-37
    chassis_number VARCHAR(30),
    engine_number VARCHAR(30),
    colour_1 VARCHAR(9), colour_2 VARCHAR(9), colour_3 VARCHAR(9),
    fuel_used VARCHAR(9), cylinder_capacity_cc VARCHAR(6), wheel_base VARCHAR(9),
    tyre_size_front VARCHAR(4), tyre_front_type ENUM('s','d'),
    tyre_size_rear VARCHAR(4), tyre_rear_type ENUM('s','d'),
    tyre_b_type ENUM('a','o'),
    length_cm VARCHAR(6), width_cm VARCHAR(6), height_cm VARCHAR(6),
    seating_capacity VARCHAR(3), driving_side ENUM('right','left'),
    unladen_weight VARCHAR(6), unladen_weight_unit ENUM('kg','ton'),
    gross_vehicle_weight VARCHAR(6), gvw_unit ENUM('kg','ton'),
    pay_load VARCHAR(4), pay_load_unit ENUM('kg','ton'),
    certified_by ENUM('cmt','agent','dealer','district'),
    inspection_name VARCHAR(60),
    import_licence ENUM('yes','no'),
    import_licence_number VARCHAR(30),
    import_licence_date DATE,
    special_conditions VARCHAR(120),

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);