-- Nzeru Lab: complete schema (MySQL 5.7+/MariaDB 10.3+). Safe to re-import: every table uses IF NOT EXISTS.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `password` VARCHAR(255) NULL,
  `profile_type` VARCHAR(30) NOT NULL DEFAULT 'independent',
  `research_topic` VARCHAR(255) NULL,
  `industry` VARCHAR(120) NULL,
  `phone` VARCHAR(30) NULL,
  `country` VARCHAR(80) NULL DEFAULT 'Malawi',
  `billing_region` VARCHAR(15) NOT NULL DEFAULT 'local',
  `plan` VARCHAR(20) NOT NULL DEFAULT 'free',
  `plan_expires_at` TIMESTAMP NULL DEFAULT NULL,
  `institution_id` BIGINT UNSIGNED NULL,
  `is_admin` TINYINT(1) NOT NULL DEFAULT 0,
  `wallet_balance` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `respondent_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `respondent_profile` LONGTEXT NULL,
  `mfa_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `mfa_secret` TEXT NULL,
  `ai_flagged` TINYINT(1) NOT NULL DEFAULT 0,
  `google_id` VARCHAR(100) NULL,
  `linkedin_id` VARCHAR(100) NULL,
  `avatar` VARCHAR(500) NULL,
  `onboarded_at` TIMESTAMP NULL DEFAULT NULL,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `remember_token` VARCHAR(100) NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_institution_id_index` (`institution_id`),
  KEY `users_plan_index` (`plan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` VARCHAR(255) NOT NULL,
  `tokenable_id` BIGINT UNSIGNED NOT NULL,
  `name` TEXT NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `abilities` TEXT NULL,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_index` (`tokenable_type`,`tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `institutions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `join_code` VARCHAR(20) NOT NULL,
  `brand_color` VARCHAR(9) NOT NULL DEFAULT '#0E7C86',
  `logo_url` VARCHAR(500) NULL,
  `seats` INT NOT NULL DEFAULT 50,
  `owner_id` BIGINT UNSIGNED NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `institutions_slug_unique` (`slug`),
  UNIQUE KEY `institutions_join_code_unique` (`join_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `institution_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `institution_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `questions` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `institution_templates_institution_id_index` (`institution_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `surveys` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `thank_you` TEXT NULL,
  `slug` VARCHAR(40) NOT NULL,
  `status` VARCHAR(15) NOT NULL DEFAULT 'draft',
  `closes_at` TIMESTAMP NULL DEFAULT NULL,
  `sincerity_checked_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `surveys_slug_unique` (`slug`),
  KEY `surveys_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `survey_questions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` BIGINT UNSIGNED NOT NULL,
  `type` VARCHAR(20) NOT NULL DEFAULT 'text',
  `label` VARCHAR(500) NOT NULL,
  `options` LONGTEXT NULL,
  `required` TINYINT(1) NOT NULL DEFAULT 1,
  `position` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_questions_survey_id_index` (`survey_id`),
  CONSTRAINT `survey_questions_survey_fk` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `survey_responses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` BIGINT UNSIGNED NOT NULL,
  `respondent_id` BIGINT UNSIGNED NULL,
  `order_id` BIGINT UNSIGNED NULL,
  `source` VARCHAR(15) NOT NULL DEFAULT 'direct',
  `answers` LONGTEXT NULL,
  `sincerity_score` TINYINT UNSIGNED NULL,
  `status` VARCHAR(15) NOT NULL DEFAULT 'accepted',
  `flag_reason` VARCHAR(255) NULL,
  `paid_out` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_hash` CHAR(64) NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_responses_survey_id_index` (`survey_id`),
  KEY `survey_responses_respondent_id_index` (`respondent_id`),
  KEY `survey_responses_order_id_index` (`order_id`),
  CONSTRAINT `survey_responses_survey_fk` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `marketplace_orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `quantity` INT NOT NULL,
  `delivered` INT NOT NULL DEFAULT 0,
  `price_per_response` DECIMAL(12,2) NOT NULL,
  `gross_amount` DECIMAL(14,2) NOT NULL,
  `platform_fee` DECIMAL(14,2) NOT NULL,
  `respondent_pool` DECIMAL(14,2) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending_payment',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `marketplace_orders_survey_id_index` (`survey_id`),
  KEY `marketplace_orders_user_id_index` (`user_id`),
  KEY `marketplace_orders_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `type` VARCHAR(20) NOT NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `balance_after` DECIMAL(14,2) NOT NULL,
  `description` VARCHAR(255) NULL,
  `reference` VARCHAR(100) NULL,
  `meta` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `wallet_transactions_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `method` VARCHAR(30) NOT NULL,
  `account` VARCHAR(120) NOT NULL,
  `status` VARCHAR(15) NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL,
  `processed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `withdrawals_user_id_index` (`user_id`),
  KEY `withdrawals_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `provider` VARCHAR(20) NOT NULL,
  `tx_ref` VARCHAR(60) NOT NULL,
  `purpose` VARCHAR(30) NOT NULL,
  `purpose_id` BIGINT UNSIGNED NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `currency` CHAR(3) NOT NULL DEFAULT 'MWK',
  `region` VARCHAR(15) NOT NULL DEFAULT 'local',
  `interval_unit` VARCHAR(10) NULL,
  `status` VARCHAR(15) NOT NULL DEFAULT 'pending',
  `provider_ref` VARCHAR(191) NULL,
  `meta` LONGTEXT NULL,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `fulfilled_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_tx_ref_unique` (`tx_ref`),
  KEY `payments_user_id_index` (`user_id`),
  KEY `payments_status_index` (`status`),
  KEY `payments_purpose_index` (`purpose`,`purpose_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `plan` VARCHAR(20) NOT NULL DEFAULT 'pro',
  `interval_unit` VARCHAR(10) NOT NULL DEFAULT 'month',
  `provider` VARCHAR(20) NOT NULL,
  `provider_ref` VARCHAR(191) NULL,
  `amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `currency` CHAR(3) NOT NULL DEFAULT 'MWK',
  `status` VARCHAR(15) NOT NULL DEFAULT 'active',
  `starts_at` TIMESTAMP NULL DEFAULT NULL,
  `ends_at` TIMESTAMP NULL DEFAULT NULL,
  `renewal_notified_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subscriptions_user_id_index` (`user_id`),
  KEY `subscriptions_status_ends_index` (`status`,`ends_at`),
  KEY `subscriptions_provider_ref_index` (`provider_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sincerity_checks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `payment_id` BIGINT UNSIGNED NULL,
  `total` INT NOT NULL DEFAULT 0,
  `flagged` INT NOT NULL DEFAULT 0,
  `ai_used` TINYINT(1) NOT NULL DEFAULT 0,
  `status` VARCHAR(15) NOT NULL DEFAULT 'done',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sincerity_checks_survey_id_index` (`survey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cvs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `type` VARCHAR(20) NOT NULL DEFAULT 'chronological',
  `data` LONGTEXT NULL,
  `ai_data` LONGTEXT NULL,
  `polish_count` INT NOT NULL DEFAULT 0,
  `unlocked_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cvs_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ai_usage_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `feature` VARCHAR(30) NOT NULL,
  `model` VARCHAR(80) NULL,
  `input_tokens` INT NOT NULL DEFAULT 0,
  `output_tokens` INT NOT NULL DEFAULT 0,
  `cost_usd` DECIMAL(10,6) NOT NULL DEFAULT 0,
  `counts_quota` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ai_usage_logs_user_created_index` (`user_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ai_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role` VARCHAR(12) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ai_messages_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `key` VARCHAR(100) NOT NULL,
  `value` LONGTEXT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;