CREATE TABLE IF NOT EXISTS `kyc_flows` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `domain_name` VARCHAR(255) NOT NULL,
    `contact_id` VARCHAR(100) NOT NULL,
    `contact_name` VARCHAR(255) DEFAULT '',
    `email` VARCHAR(255) NOT NULL,
    `mobile` VARCHAR(40) NOT NULL,
    `email_verified_at` DATETIME NULL,
    `mobile_verified_at` DATETIME NULL,
    `digio_request_id` VARCHAR(150) NULL,
    `status` VARCHAR(40) NOT NULL DEFAULT 'created',
    `kyc_completed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `otp_verifications` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `kyc_flow_id` INT UNSIGNED NOT NULL,
    `channel` VARCHAR(20) NOT NULL,
    `recipient` VARCHAR(255) NOT NULL,
    `otp_hash` VARCHAR(255) NOT NULL,
    `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
    `expires_at` DATETIME NOT NULL,
    `verified_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_otp_flow_channel` (`kyc_flow_id`, `channel`),
    CONSTRAINT `fk_otp_kyc_flow`
        FOREIGN KEY (`kyc_flow_id`) REFERENCES `kyc_flows` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `kyc_entries` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `kyc_flow_id` INT UNSIGNED NOT NULL,
    `domain_name` VARCHAR(255) NOT NULL,
    `contact_id` VARCHAR(100) NOT NULL,
    `verified_name` VARCHAR(255) DEFAULT '',
    `verified_email` VARCHAR(255) DEFAULT '',
    `verified_mobile` VARCHAR(40) DEFAULT '',
    `verified_address` TEXT,
    `verified_city` VARCHAR(120) DEFAULT '',
    `verified_postal_code` VARCHAR(20) DEFAULT '',
    `document_id` VARCHAR(120) DEFAULT '',
    `raw_response` LONGTEXT,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_kyc_entries_domain` (`domain_name`),
    CONSTRAINT `fk_entry_kyc_flow`
        FOREIGN KEY (`kyc_flow_id`) REFERENCES `kyc_flows` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;