CREATE DATABASE IF NOT EXISTS esign
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE esign;


CREATE TABLE IF NOT EXISTS esign_transaction
(
    id INT NOT NULL AUTO_INCREMENT,

    prn VARCHAR(64) NOT NULL,

    txn_id VARCHAR(255) NOT NULL,

    application_code VARCHAR(100) NOT NULL,

    asp_id VARCHAR(100) NOT NULL,

    original_pdf VARCHAR(255) DEFAULT NULL,

    person_name VARCHAR(255) DEFAULT NULL,

    person_designation VARCHAR(255) DEFAULT NULL,

    person_location VARCHAR(255) DEFAULT NULL,

    status VARCHAR(50) DEFAULT NULL,

    response_code VARCHAR(50) DEFAULT NULL,

    response_message TEXT DEFAULT NULL,

    signed_pdf VARCHAR(255) DEFAULT NULL,

    created_datetime DATETIME NOT NULL,

    updated_datetime DATETIME NOT NULL,

    PRIMARY KEY (id),

    KEY idx_prn (prn),

    KEY idx_txn_id (txn_id)

)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS esign_response_log
(
    id INT NOT NULL AUTO_INCREMENT,

    request_id VARCHAR(100) NOT NULL,

    request_method VARCHAR(20) DEFAULT NULL,

    request_uri TEXT DEFAULT NULL,

    remote_ip VARCHAR(100) DEFAULT NULL,

    content_type VARCHAR(255) DEFAULT NULL,

    request_body LONGTEXT DEFAULT NULL,

    received_datetime DATETIME NOT NULL,

    PRIMARY KEY (id),

    KEY idx_request_id (request_id),

    KEY idx_received_datetime (received_datetime)

)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;