-- ==========================================
-- LuxBudget MySQL Schema for phpMyAdmin
-- ==========================================

-- Create Database (uncomment if you want phpMyAdmin to create it)
-- CREATE DATABASE IF NOT EXISTS luxbudget;
-- USE luxbudget;

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL UNIQUE, -- Local user identifier or Firebase UID
  `email` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Budgets Table (Target limits per category per month)
CREATE TABLE IF NOT EXISTS `budgets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `category` VARCHAR(255) NOT NULL,
  `amount` INT NOT NULL, -- Amount in cents (e.g. 1000 is 10.00 €)
  `month` VARCHAR(10) NOT NULL, -- Format: YYYY-MM
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_budgets_user` FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Transactions Table (Income and expenses)
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `type` VARCHAR(50) NOT NULL, -- 'income' or 'expense'
  `amount` INT NOT NULL, -- Amount in cents (e.g. 4550 is 45.50 €)
  `category` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `date` VARCHAR(10) NOT NULL, -- Format: YYYY-MM-DD
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) 
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- Starter Seed Data for Test User
-- ==========================================

-- Insert a local test user (Jean-Philippe D.)
INSERT INTO `users` (`id`, `uid`, `email`, `created_at`) 
VALUES (1, 'local-user-jp', 'jean.philippe@example.com', CURRENT_TIMESTAMP)
ON DUPLICATE KEY UPDATE `email` = `email`;

-- Insert default envelopes/budgets for July 2026
INSERT INTO `budgets` (`id`, `user_id`, `category`, `amount`, `month`) VALUES
(1, 1, 'Logement & Charges', 100000, '2026-07'),
(2, 1, 'Alimentation', 50000, '2026-07'),
(3, 1, 'Transports & Loisirs', 40000, '2026-07')
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`);

-- Insert standard transaction examples
INSERT INTO `transactions` (`user_id`, `type`, `amount`, `category`, `description`, `date`) VALUES
(1, 'income', 315000, 'Salaire', 'Virement Mensuel Salaire', '2026-07-05'),
(1, 'expense', 95000, 'Logement & Charges', 'Loyer Appartement', '2026-07-01'),
(1, 'expense', 7645, 'Alimentation', 'Supermarché Central', '2026-07-20'),
(1, 'expense', 1499, 'Abonnement', 'Abonnement SaaS Premium', '2026-07-12'),
(1, 'expense', 8210, 'Transports & Loisirs', 'Station Service Total', '2026-07-04');