-- =============================================================
-- Personal AI Assistant - MySQL Database Schema
-- =============================================================
-- Run this file once after creating your MySQL database.
-- Compatible with MySQL 5.7+ / MariaDB 10.3+
-- =============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -------------------------------------------------------------
-- Table: users
-- Stores Firebase-authenticated user records.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `uid`        VARCHAR(128)  NOT NULL COMMENT 'Firebase UID',
    `email`      VARCHAR(255)  NOT NULL COMMENT 'User email address',
    `name`       VARCHAR(255)  DEFAULT NULL COMMENT 'Display name (optional)',
    `created_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation time',
    PRIMARY KEY (`uid`),
    INDEX `idx_users_email` (`email`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Firebase authenticated users';

-- -------------------------------------------------------------
-- Table: transactions
-- Stores parsed SMS/notification bank transactions per user.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
    `id`          INT           NOT NULL AUTO_INCREMENT,
    `user_uid`    VARCHAR(128)  NOT NULL COMMENT 'Reference to users.uid',
    `amount`      DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Transaction amount in INR',
    `merchant`    VARCHAR(255)  DEFAULT NULL COMMENT 'Merchant or payee name',
    `type`        ENUM('debit','credit') NOT NULL COMMENT 'Transaction direction',
    `txn_date`    VARCHAR(50)   DEFAULT NULL COMMENT 'Raw date string from SMS',
    `ref_number`  VARCHAR(100)  DEFAULT NULL COMMENT 'Bank reference/UPI ID for dedup',
    `raw_text`    TEXT          DEFAULT NULL COMMENT 'Original SMS/notification body',
    `source`      ENUM('sms','notification') NOT NULL DEFAULT 'sms' COMMENT 'Data origin',
    `created_at`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record insertion time',
    PRIMARY KEY (`id`),
    INDEX `idx_txn_user_uid`   (`user_uid`),
    INDEX `idx_txn_ref_number` (`user_uid`, `ref_number`),
    INDEX `idx_txn_type`       (`user_uid`, `type`),
    INDEX `idx_txn_created`    (`user_uid`, `created_at`),
    CONSTRAINT `fk_transactions_user`
        FOREIGN KEY (`user_uid`) REFERENCES `users` (`uid`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Parsed bank transactions from SMS and notifications';

-- -------------------------------------------------------------
-- Table: notifications
-- Stores raw device notifications per user.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
    `id`               INT          NOT NULL AUTO_INCREMENT,
    `user_uid`         VARCHAR(128) NOT NULL COMMENT 'Reference to users.uid',
    `app_name`         VARCHAR(100) DEFAULT NULL COMMENT 'Source application package/name',
    `title`            VARCHAR(255) DEFAULT NULL COMMENT 'Notification title',
    `message`          TEXT         DEFAULT NULL COMMENT 'Notification body text',
    `notif_timestamp`  BIGINT       DEFAULT NULL COMMENT 'Original notification epoch ms',
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record insertion time',
    PRIMARY KEY (`id`),
    INDEX `idx_notif_user_uid`  (`user_uid`),
    INDEX `idx_notif_app_name`  (`user_uid`, `app_name`),
    INDEX `idx_notif_timestamp` (`user_uid`, `notif_timestamp`),
    CONSTRAINT `fk_notifications_user`
        FOREIGN KEY (`user_uid`) REFERENCES `users` (`uid`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Raw device notifications received from the Android app';

-- -------------------------------------------------------------
-- Table: chat_messages
-- Stores conversation history between user and Gemini AI.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `chat_messages` (
    `id`         INT          NOT NULL AUTO_INCREMENT,
    `user_uid`   VARCHAR(128) NOT NULL COMMENT 'Reference to users.uid',
    `role`       ENUM('user','model') NOT NULL COMMENT 'Who sent the message',
    `content`    TEXT         NOT NULL COMMENT 'Message body',
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Message timestamp',
    PRIMARY KEY (`id`),
    INDEX `idx_chat_user_uid` (`user_uid`),
    INDEX `idx_chat_created`  (`user_uid`, `created_at`),
    CONSTRAINT `fk_chat_messages_user`
        FOREIGN KEY (`user_uid`) REFERENCES `users` (`uid`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Gemini AI conversation history per user';

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================================
-- End of schema
-- =============================================================