-- Masher Hisab (মাসিক হিসাব) Database Schema
-- Target Base: https://month.digibd.us
-- Package: com.digib.month

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `phone_number` VARCHAR(32) DEFAULT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(32) NOT NULL DEFAULT 'user',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_phone` (`phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. User Authentication Tokens
CREATE TABLE IF NOT EXISTS `user_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token_hash` CHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_token_hash` (`token_hash`),
  KEY `idx_user_tokens_user` (`user_id`),
  KEY `idx_user_tokens_expires` (`expires_at`),
  CONSTRAINT `fk_user_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Money Tracker Snapshots (Cloud Backup for Monthly Incomes & Expenses)
CREATE TABLE IF NOT EXISTS `user_money_tracker_snapshots` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `records_json` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_money_tracker_user` (`user_id`),
  CONSTRAINT `fk_money_tracker_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Smart Note Reports (Daily Work Diary, Notes & Photos)
CREATE TABLE IF NOT EXISTS `smart_note_reports` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `date_key` VARCHAR(32) NOT NULL,
  `report_json` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_smart_notes_user_date` (`user_id`, `date_key`),
  KEY `idx_smart_notes_user_updated` (`user_id`, `updated_at`),
  CONSTRAINT `fk_smart_notes_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. System Money Categories (Income / Expense Category Management)
CREATE TABLE IF NOT EXISTS `system_money_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_type` ENUM('income', 'expense') NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cat_type_name` (`category_type`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. User App Settings / Preferences Cloud Store
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `settings_json` LONGTEXT NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_settings` (`user_id`),
  CONSTRAINT `fk_user_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- Default Categories Seeding
INSERT IGNORE INTO `system_money_categories` (`category_type`, `name`, `sort_order`) VALUES
('income', 'Salary / বেতন', 1),
('income', 'Business Profit / ব্যবসা', 2),
('income', 'Bonus / বোনাস', 3),
('income', 'Advance Recovery / অগ্রিম ফেরত', 4),
('income', 'Client Payment / গ্রাহক পরিশোধ', 5),
('income', 'Freelance / ফ্রিল্যান্সিং', 6),
('income', 'Investment Return / বিনিয়োগ লাভ', 7),
('income', 'Other Income / অন্যান্য আয়', 8),
('expense', 'House Rent / বাড়ি ভাড়া', 1),
('expense', 'Groceries / বাজার খরচ', 2),
('expense', 'Utility Bills / বিদ্যুৎ-পানি-গ্যাস', 3),
('expense', 'Transport & Fuel / যাতায়াত ও জ্বালানি', 4),
('expense', 'Family & Kids / পরিবার ও সন্তান', 5),
('expense', 'Medical & Health / চিকিৎসা ও ওষুধ', 6),
('expense', 'Food & Dining / খাওয়া-দাওয়া', 7),
('expense', 'Mobile & Internet / রিচার্জ ও ইন্টারনেট', 8),
('expense', 'Shopping & Clothing / কেনাকাটা', 9),
('expense', 'Office / Site Supply / সাইট খরচ', 10),
('expense', 'Entertainment / বিনোদন', 11),
('expense', 'Other Expense / অন্যান্য খরচ', 12);