-- ═══════════════════════════════════════════════════
--  TaskBit — Bank Statement AI Tables
--  Created: May 2026
-- ═══════════════════════════════════════════════════

-- Table 1: One row per uploaded statement file
CREATE TABLE IF NOT EXISTS `bank_statements` (
  `id`              int NOT NULL AUTO_INCREMENT,
  `user_id`         varchar(250) DEFAULT NULL,          -- device_id same as ai_scans
  `file_path`       varchar(500) DEFAULT NULL,          -- uploaded file path on server
  `file_name`       varchar(255) DEFAULT NULL,          -- original filename
  `file_type`       enum('pdf','xls','xlsx','csv') DEFAULT 'pdf',
  `bank_name`       varchar(100) DEFAULT NULL,          -- AI detected: ICICI, HDFC, SBI etc
  `account_no`      varchar(50)  DEFAULT NULL,          -- masked: XX1234
  `period_from`     date DEFAULT NULL,                  -- statement start date
  `period_to`       date DEFAULT NULL,                  -- statement end date
  `total_debit`     decimal(12,2) DEFAULT 0.00,
  `total_credit`    decimal(12,2) DEFAULT 0.00,
  `txn_count`       int DEFAULT 0,                      -- number of transactions parsed
  `ai_model`        varchar(100) DEFAULT NULL,
  `ai_provider`     varchar(50)  DEFAULT NULL,
  `prompt_tokens`   int DEFAULT 0,
  `completion_tokens` int DEFAULT 0,
  `cost_usd`        decimal(10,6) DEFAULT 0.000000,
  `parse_status`    enum('pending','success','failed') DEFAULT 'pending',
  `error_msg`       text DEFAULT NULL,
  `created_at`      timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id`    (`user_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_bank_name`  (`bank_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- Table 2: Individual transactions (45 rows per ICICI statement etc)
CREATE TABLE IF NOT EXISTS `bank_transactions` (
  `id`            int NOT NULL AUTO_INCREMENT,
  `statement_id`  int NOT NULL,                        -- FK → bank_statements.id
  `user_id`       varchar(250) DEFAULT NULL,
  `txn_date`      date DEFAULT NULL,
  `merchant`      varchar(255) DEFAULT NULL,           -- clean name: "Zomato", "Haque Vari"
  `remarks`       text DEFAULT NULL,                   -- original raw text from statement
  `category`      varchar(100) DEFAULT NULL,           -- "Food & Dining", "EMI & Loans" etc
  `debit`         decimal(12,2) DEFAULT 0.00,
  `credit`        decimal(12,2) DEFAULT 0.00,
  `balance`       decimal(12,2) DEFAULT 0.00,
  `txn_type`      enum('debit','credit') DEFAULT 'debit',
  `is_corrected`  tinyint(1) DEFAULT 0,               -- 1 = user manually changed category
  `created_at`    timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_statement_id` (`statement_id`),
  KEY `idx_user_id`      (`user_id`),
  KEY `idx_category`     (`category`),
  KEY `idx_txn_date`     (`txn_date`),
  KEY `idx_merchant`     (`merchant`),
  FOREIGN KEY (`statement_id`) REFERENCES `bank_statements`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
