-- Base: balance_de_gastos
CREATE DATABASE IF NOT EXISTS balance_de_gastos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE balance_de_gastos;

-- Cuentas (ej: Caja, Cuenta bancaria, Tarjeta)
CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  name VARCHAR(150) NOT NULL,
  currency VARCHAR(10) DEFAULT 'ARS',
  balance DECIMAL(15,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Categorías (Comida, Transporte, Sueldo...)
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  name VARCHAR(150) NOT NULL,
  color VARCHAR(7) DEFAULT '#cccccc',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Transacciones (gasto/ingreso)
CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  account_id INT NOT NULL,
  category_id INT NULL,
  type ENUM('expense','income') NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency VARCHAR(10) DEFAULT 'ARS',
  date DATE NOT NULL,
  description TEXT,
  tags VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Presupuestos (opcional)
CREATE TABLE budgets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  category_id INT NOT NULL,
  period ENUM('monthly','yearly') DEFAULT 'monthly',
  amount DECIMAL(15,2) NOT NULL,
  start_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO accounts (user_id, name, currency, balance) VALUES
(1, 'Caja', 'ARS', 0),
(1, 'Banco BBVA', 'ARS', 0);

INSERT INTO categories (user_id, name, color) VALUES
(1, 'Alimentos', '#f39c12'),
(1, 'Transporte', '#3498db'),
(1, 'Sueldo', '#2ecc71');

INSERT INTO transactions (user_id, account_id, category_id, type, amount, date, description, tags)
VALUES
(1, 1, 1, 'expense', 1200.00, '2025-09-15', 'Supermercado', 'comida,mes'),
(1, 2, 3, 'income', 50000.00, '2025-09-01', 'Sueldo septiembre', 'ingreso');
