GestionLocative/backend/src/db.js

const Database = require('better-sqlite3');
const path = require('path');

const db = new Database(path.join(__dirname, '../data/gestion_locative.db'));

db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS properties (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    address TEXT NOT NULL,
    city TEXT NOT NULL,
    zip_code TEXT NOT NULL,
    type TEXT NOT NULL,
    rooms INTEGER,
    area REAL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS tenants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS leases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    property_id INTEGER NOT NULL,
    tenant_id INTEGER NOT NULL,
    start_date TEXT NOT NULL,
    end_date TEXT,
    rent_amount REAL NOT NULL,
    charges_amount REAL NOT NULL DEFAULT 0,
    deposit_amount REAL NOT NULL DEFAULT 0,
    payment_day INTEGER NOT NULL DEFAULT 1,
    active INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
  );

  CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    lease_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    period_month INTEGER NOT NULL,
    period_year INTEGER NOT NULL,
    rent_paid REAL NOT NULL,
    charges_paid REAL NOT NULL DEFAULT 0,
    payment_date TEXT NOT NULL,
    payment_method TEXT NOT NULL DEFAULT 'virement',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lease_id) REFERENCES leases(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS charges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    property_id INTEGER NOT NULL,
    category TEXT NOT NULL,
    label TEXT NOT NULL,
    amount REAL NOT NULL,
    date TEXT NOT NULL,
    year INTEGER NOT NULL,
    invoice_path TEXT,
    invoice_name TEXT,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (property_id) REFERENCES properties(id)
  );
`);

// Add profile columns to users if they don't exist yet
['first_name', 'last_name', 'address', 'phone'].forEach(col => {
  try { db.exec(`ALTER TABLE users ADD COLUMN ${col} TEXT`); } catch {}
});

// Lease revisions history
db.exec(`
  CREATE TABLE IF NOT EXISTS lease_revisions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    lease_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    effective_date TEXT NOT NULL,
    rent_amount REAL NOT NULL,
    charges_amount REAL NOT NULL DEFAULT 0,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lease_id) REFERENCES leases(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

// Add recoverable column to charges if missing
try { db.exec('ALTER TABLE charges ADD COLUMN recoverable INTEGER NOT NULL DEFAULT 1'); } catch {}

// Add charge_regularization column to payments if missing
try { db.exec('ALTER TABLE payments ADD COLUMN charge_regularization REAL DEFAULT 0'); } catch {}

// Prorata columns on payments
try { db.exec('ALTER TABLE payments ADD COLUMN is_prorata INTEGER DEFAULT 0'); } catch {}
try { db.exec('ALTER TABLE payments ADD COLUMN prorata_days INTEGER'); } catch {}
try { db.exec('ALTER TABLE payments ADD COLUMN prorata_total_days INTEGER'); } catch {}

// Regularizations from annual exercise closure
db.exec(`
  CREATE TABLE IF NOT EXISTS charge_regularizations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    lease_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    property_id INTEGER NOT NULL,
    year INTEGER NOT NULL,
    amount REAL NOT NULL,
    notes TEXT,
    applied_payment_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lease_id) REFERENCES leases(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

module.exports = db;