GestionLocative/backend/src/db.js

const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.PGHOST || 'localhost',
  port: parseInt(process.env.PGPORT || '5432'),
  user: process.env.PGUSER || 'postgres',
  password: process.env.PGPASSWORD || 'postgres',
  database: process.env.PGDATABASE || 'gestion_locative',
});

async function initDb() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email TEXT UNIQUE NOT NULL,
      password TEXT,
      name TEXT NOT NULL,
      first_name TEXT,
      last_name TEXT,
      address TEXT,
      phone TEXT,
      oidc_sub TEXT UNIQUE,
      created_at TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS owners (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id),
      type TEXT NOT NULL DEFAULT 'physique',
      first_name TEXT,
      last_name TEXT,
      company_name TEXT,
      siret TEXT,
      address TEXT,
      zip_code TEXT,
      city TEXT,
      email TEXT,
      phone TEXT,
      created_at TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS properties (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id),
      owner_id INTEGER REFERENCES owners(id),
      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 TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS tenants (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id),
      first_name TEXT NOT NULL,
      last_name TEXT NOT NULL,
      email TEXT,
      phone TEXT,
      created_at TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS leases (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id),
      property_id INTEGER NOT NULL REFERENCES properties(id),
      tenant_id INTEGER NOT NULL REFERENCES tenants(id),
      owner_id INTEGER REFERENCES owners(id),
      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 TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS payments (
      id SERIAL PRIMARY KEY,
      lease_id INTEGER NOT NULL REFERENCES leases(id),
      user_id INTEGER NOT NULL REFERENCES users(id),
      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,
      charge_regularization REAL DEFAULT 0,
      is_prorata INTEGER DEFAULT 0,
      prorata_days INTEGER,
      prorata_total_days INTEGER,
      created_at TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS charges (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id),
      property_id INTEGER NOT NULL REFERENCES properties(id),
      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,
      recoverable INTEGER NOT NULL DEFAULT 1,
      created_at TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS lease_revisions (
      id SERIAL PRIMARY KEY,
      lease_id INTEGER NOT NULL REFERENCES leases(id),
      user_id INTEGER NOT NULL REFERENCES users(id),
      effective_date TEXT NOT NULL,
      rent_amount REAL NOT NULL,
      charges_amount REAL NOT NULL DEFAULT 0,
      notes TEXT,
      created_at TIMESTAMP DEFAULT NOW()
    );

    CREATE TABLE IF NOT EXISTS charge_regularizations (
      id SERIAL PRIMARY KEY,
      lease_id INTEGER NOT NULL REFERENCES leases(id),
      user_id INTEGER NOT NULL REFERENCES users(id),
      property_id INTEGER NOT NULL,
      year INTEGER NOT NULL,
      amount REAL NOT NULL,
      notes TEXT,
      applied_payment_id INTEGER,
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  // Invoice scanning tables
  await pool.query(`
    CREATE TABLE IF NOT EXISTS scanned_invoices (
      id SERIAL PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id),
      property_id INTEGER NOT NULL REFERENCES properties(id),
      file_path TEXT NOT NULL,
      file_hash TEXT NOT NULL,
      year INTEGER,
      detected_supplier TEXT,
      detected_category TEXT,
      detected_amount REAL,
      detected_label TEXT,
      status TEXT NOT NULL DEFAULT 'pending',
      approved_at TIMESTAMP,
      charge_id INTEGER REFERENCES charges(id),
      notes TEXT,
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  // Idempotent migrations for columns added after initial schema
  await pool.query(`ALTER TABLE properties ADD COLUMN IF NOT EXISTS scan_directory TEXT`);
  await pool.query(`ALTER TABLE properties ADD COLUMN IF NOT EXISTS scan_cron_enabled INTEGER DEFAULT 0`);
  await pool.query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS first_name TEXT`);
  await pool.query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS last_name TEXT`);
  await pool.query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS address TEXT`);
  await pool.query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT`);
  await pool.query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS oidc_sub TEXT UNIQUE`);
  await pool.query(`ALTER TABLE users ALTER COLUMN password DROP NOT NULL`);
  await pool.query(`ALTER TABLE leases ADD COLUMN IF NOT EXISTS owner_id INTEGER REFERENCES owners(id)`);
  await pool.query(`ALTER TABLE leases ADD COLUMN IF NOT EXISTS irl_reference_value REAL`);
  await pool.query(`ALTER TABLE leases ADD COLUMN IF NOT EXISTS irl_reference_quarter INTEGER`);
  await pool.query(`ALTER TABLE leases ADD COLUMN IF NOT EXISTS irl_reference_year INTEGER`);
  await pool.query(`ALTER TABLE charges ADD COLUMN IF NOT EXISTS recoverable INTEGER NOT NULL DEFAULT 1`);
  await pool.query(`ALTER TABLE charges ADD COLUMN IF NOT EXISTS recoverable_type TEXT DEFAULT 'recoverable'`);
  // Backfill recoverable_type from legacy integer column
  await pool.query(`UPDATE charges SET recoverable_type = 'none' WHERE recoverable = 0 AND (recoverable_type IS NULL OR recoverable_type = 'recoverable')`);
  await pool.query(`ALTER TABLE payments ADD COLUMN IF NOT EXISTS charge_regularization REAL DEFAULT 0`);
  await pool.query(`ALTER TABLE payments ADD COLUMN IF NOT EXISTS is_prorata INTEGER DEFAULT 0`);
  await pool.query(`ALTER TABLE payments ADD COLUMN IF NOT EXISTS prorata_days INTEGER`);
  await pool.query(`ALTER TABLE payments ADD COLUMN IF NOT EXISTS prorata_total_days INTEGER`);
}

initDb().catch(err => {
  console.error('Database initialization error:', err);
  process.exit(1);
});

module.exports = pool;