setup.sql

-- ==========================================================
-- Blank Database Initializer Schema Template
-- Project: Enterprise Personal Finance Manager
-- Target Database: expenses_tracker
-- ==========================================================

CREATE DATABASE IF NOT EXISTS `expenses_tracker` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `expenses_tracker`;

-- --------------------------------------------------------
-- Table structure for table `accounts`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(50) DEFAULT 'cash',
  `credit_limit` decimal(10,2) DEFAULT 0.00,
  `principal_amount` decimal(10,2) DEFAULT 0.00,
  `emi_amount` decimal(10,2) DEFAULT 0.00,
  `due_date_day` int(11) DEFAULT 1,
  `due_day` int(11) DEFAULT 0,
  `is_hidden` int(11) DEFAULT 0,
  `icon` mediumtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default base account
INSERT INTO `accounts` (`id`, `name`, `type`, `credit_limit`, `principal_amount`, `emi_amount`, `due_date_day`, `due_day`, `icon`) 
VALUES (1, 'Personal', 'cash', 0.00, 0.00, 0.00, 1, 0, '')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- --------------------------------------------------------
-- Table structure for table `expenses`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT 1,
  `type` varchar(50) DEFAULT 'expense',
  `entity` varchar(100) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_account_date` (`account_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `settings`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed default unified application parameters
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('ai_model', 'llama3.2:1b'),
('categories_expense', '["Food","Transport","Utilities","Entertainment","Shopping","Salary","Loan","Other"]'),
('categories_income', '["Salary","Freelance","Investments","Gift","Loan","Other"]')
ON DUPLICATE KEY UPDATE `setting_key`=`setting_key`;