#!/usr/bin/env python3
"""RH Planete: self-contained PostgreSQL schema migration (2026-10-10).
Run in the cPanel Python environment. No Flask/project files required.
Creates/updates schema only; does not copy employee data or create accounts.
Back up any existing database before running. All DDL runs in one transaction.
"""
import argparse
import getpass
import os
import sys

SCHEMA_SQL = "-- Schéma PostgreSQL pour RH Planète (rh_planete)\n\nCREATE TABLE IF NOT EXISTS users (\n    id SERIAL PRIMARY KEY,\n    username VARCHAR(64) UNIQUE NOT NULL,\n    full_name VARCHAR(128) NOT NULL,\n    first_name VARCHAR(64),\n    last_name VARCHAR(64),\n    password_hash VARCHAR(256) NOT NULL,\n    role VARCHAR(32) NOT NULL DEFAULT 'employee' CHECK (role IN ('employee', 'manager', 'hr', 'admin')),\n    team VARCHAR(64) NOT NULL DEFAULT '',\n    position_title VARCHAR(128) DEFAULT '',\n    cin_nic VARCHAR(32) DEFAULT '',\n    phone VARCHAR(32) DEFAULT '',\n    email VARCHAR(128) DEFAULT '',\n    postal_address TEXT NOT NULL DEFAULT '',\n    address_line1 TEXT NOT NULL DEFAULT '',\n    address_line2 TEXT NOT NULL DEFAULT '',\n    postal_code TEXT NOT NULL DEFAULT '',\n    city TEXT NOT NULL DEFAULT '',\n    country TEXT NOT NULL DEFAULT '',\n    emergency_contact_name TEXT NOT NULL DEFAULT '',\n    emergency_contact_phone TEXT NOT NULL DEFAULT '',\n    bank_name VARCHAR(64) DEFAULT '',\n    bank_account VARCHAR(64) DEFAULT '',\n    basic_salary NUMERIC(12, 2) DEFAULT 0,\n    manager_id INTEGER REFERENCES users(id) ON DELETE SET NULL,\n    start_date DATE,\n    active BOOLEAN NOT NULL DEFAULT TRUE,\n    payroll_admin BOOLEAN NOT NULL DEFAULT FALSE,\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE IF NOT EXISTS balances (\n    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    year INTEGER NOT NULL,\n    annual_opening NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    annual_entitlement NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    annual_used NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    sick_opening NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    sick_entitlement NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    sick_used NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    PRIMARY KEY(user_id, year)\n);\n\nCREATE TABLE IF NOT EXISTS leave_requests (\n    id SERIAL PRIMARY KEY,\n    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    leave_type VARCHAR(32) NOT NULL CHECK(leave_type IN ('annual', 'sick', 'unplanned', 'wfh', 'unpaid', 'menstrual')),\n    start_date DATE NOT NULL,\n    end_date DATE NOT NULL,\n    day_fraction NUMERIC(3, 1) NOT NULL DEFAULT 1.0,\n    reason TEXT NOT NULL DEFAULT '',\n    attachment VARCHAR(255),\n    status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected')),\n    decided_by INTEGER REFERENCES users(id) ON DELETE SET NULL,\n    decision_note TEXT NOT NULL DEFAULT '',\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE IF NOT EXISTS weekend_work (\n    id SERIAL PRIMARY KEY,\n    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    work_date DATE NOT NULL,\n    work_type VARCHAR(32) NOT NULL CHECK(work_type IN ('saturday', 'sunday', 'on_call')),\n    intervention_hours NUMERIC(6, 2) NOT NULL DEFAULT 0,\n    notes TEXT NOT NULL DEFAULT '',\n    status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected')),\n    decided_by INTEGER REFERENCES users(id) ON DELETE SET NULL,\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE IF NOT EXISTS monthly_salaries (\n    id SERIAL PRIMARY KEY,\n    month VARCHAR(7) NOT NULL, -- format YYYY-MM\n    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    basic_salary NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    salary_increase NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    commission NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    overtime_adjustment NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    presence_bonus NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    special_bonus NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    transport NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    loan_refund NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    medical_deduction NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    notes TEXT NOT NULL DEFAULT '',\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    UNIQUE(month, user_id)\n);\n\nCREATE TABLE IF NOT EXISTS payroll_adjustments (\n    id SERIAL PRIMARY KEY,\n    month VARCHAR(7) NOT NULL,\n    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    presence_bonus NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    overtime_adjustment NUMERIC(12, 2) NOT NULL DEFAULT 0,\n    note TEXT NOT NULL DEFAULT '',\n    updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    UNIQUE(month, user_id)\n);\n\nCREATE TABLE IF NOT EXISTS audit_log (\n    id SERIAL PRIMARY KEY,\n    actor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,\n    action VARCHAR(64) NOT NULL,\n    entity VARCHAR(64) NOT NULL,\n    entity_id INTEGER,\n    details TEXT NOT NULL DEFAULT '',\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE IF NOT EXISTS public_holidays (\n    id SERIAL PRIMARY KEY,\n    country VARCHAR(4) NOT NULL CHECK(country IN ('MU', 'FR')),\n    holiday_date DATE NOT NULL,\n    name VARCHAR(128) NOT NULL,\n    UNIQUE(country, holiday_date)\n);\n\n-- Index pour optimiser les requêtes fréquentes\nCREATE INDEX IF NOT EXISTS idx_leave_requests_user ON leave_requests(user_id);\nCREATE INDEX IF NOT EXISTS idx_leave_requests_dates ON leave_requests(start_date, end_date);\nCREATE INDEX IF NOT EXISTS idx_weekend_work_user ON weekend_work(user_id);\nCREATE INDEX IF NOT EXISTS idx_weekend_work_date ON weekend_work(work_date);\nCREATE INDEX IF NOT EXISTS idx_monthly_salaries_month ON monthly_salaries(month);\nCREATE INDEX IF NOT EXISTS idx_monthly_salaries_user ON monthly_salaries(user_id);\n\n-- Explicit RH assessment of the first six months; no inference from missing events.\nCREATE TABLE IF NOT EXISTS attendance_reviews (\n    user_id INTEGER NOT NULL REFERENCES users(id),\n    service_start DATE NOT NULL,\n    status TEXT NOT NULL CHECK(status IN ('unknown','confirmed','not_met')),\n    note TEXT NOT NULL DEFAULT '',\n    reviewed_by INTEGER REFERENCES users(id),\n    reviewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    PRIMARY KEY(user_id, service_start)\n);\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN phone TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN email TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS postal_address TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN postal_address TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS address_line1 TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN address_line1 TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS address_line2 TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN address_line2 TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS postal_code TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN postal_code TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS city TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN city TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS country TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN country TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS emergency_contact_name TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN emergency_contact_name TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS emergency_contact_phone TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN emergency_contact_phone TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS birth_date TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN birth_date TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS bank_name TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN bank_name TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS bank_account TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN bank_account TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS bank_account_holder TEXT NOT NULL DEFAULT '';\nALTER TABLE users ALTER COLUMN bank_account_holder TYPE TEXT;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS account_enabled INTEGER NOT NULL DEFAULT 1;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS must_change_password INTEGER NOT NULL DEFAULT 0;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS auth_version INTEGER NOT NULL DEFAULT 0;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS birthday_public INTEGER NOT NULL DEFAULT 0;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS birthday_show_age INTEGER NOT NULL DEFAULT 0;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS birthday_email INTEGER NOT NULL DEFAULT 0;\n\nALTER TABLE users ADD COLUMN IF NOT EXISTS anniversary_public INTEGER NOT NULL DEFAULT 1;\n\nALTER TABLE leave_requests DROP CONSTRAINT IF EXISTS leave_requests_leave_type_check;\nALTER TABLE leave_requests ADD CONSTRAINT leave_requests_leave_type_check CHECK(leave_type IN ('annual','sick','unplanned','wfh','unpaid','menstrual'));\nCREATE TABLE IF NOT EXISTS job_functions (name TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 1);\nCREATE TABLE IF NOT EXISTS module_restrictions (user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, module TEXT NOT NULL, PRIMARY KEY(user_id,module));\nINSERT INTO job_functions(name) SELECT DISTINCT TRIM(position_title) FROM users WHERE position_title IS NOT NULL AND TRIM(position_title)<>'' ON CONFLICT(name) DO NOTHING;\nCREATE UNIQUE INDEX IF NOT EXISTS users_username_case_unique ON users(LOWER(username));\nCREATE UNIQUE INDEX IF NOT EXISTS users_nic_case_unique ON users(LOWER(cin_nic)) WHERE cin_nic IS NOT NULL AND TRIM(cin_nic)<>'';\nCREATE TABLE IF NOT EXISTS company_teams (name TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 1);\nINSERT INTO company_teams(name) VALUES('Planete'),('Solea'),('Viaxoft'),('Jancartier') ON CONFLICT(name) DO NOTHING;\nCREATE TABLE IF NOT EXISTS calendar_overrides (holiday_date TEXT PRIMARY KEY, name TEXT NOT NULL, non_working INTEGER NOT NULL);\nCREATE TABLE IF NOT EXISTS payroll_rates (effective_date TEXT PRIMARY KEY, presence REAL NOT NULL, saturday REAL NOT NULL, sunday REAL NOT NULL, standby REAL NOT NULL, intervention_hour REAL NOT NULL, created_by INTEGER REFERENCES users(id), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);\nCREATE TABLE IF NOT EXISTS celebration_settings (id INTEGER PRIMARY KEY, recipient TEXT NOT NULL DEFAULT '', send_time TEXT NOT NULL DEFAULT '08:00', enabled INTEGER NOT NULL DEFAULT 0);\nINSERT INTO celebration_settings(id) VALUES(1) ON CONFLICT(id) DO NOTHING;\nCREATE TABLE IF NOT EXISTS celebration_deliveries (send_date TEXT PRIMARY KEY, status TEXT NOT NULL, detail TEXT NOT NULL DEFAULT '', updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);\nCREATE TABLE IF NOT EXISTS login_limits (bucket TEXT PRIMARY KEY, attempts INTEGER NOT NULL, expires BIGINT NOT NULL);\n"


def main():
    parser = argparse.ArgumentParser(description='Create/update RH Planete PostgreSQL schema')
    parser.add_argument('--host', default=os.getenv('PGHOST','localhost'))
    parser.add_argument('--port', type=int, default=int(os.getenv('PGPORT','5432')))
    parser.add_argument('--database', default=os.getenv('PGDATABASE','n788705_rhplanete'))
    parser.add_argument('--user', default=os.getenv('PGUSER','n788705_rhplaneteprod'))
    parser.add_argument('--sslmode', choices=('prefer','require','verify-ca','verify-full'), default=os.getenv('PGSSLMODE','prefer'))
    args = parser.parse_args()
    try:
        import psycopg2
    except ImportError:
        print('Dependency missing. Run: python -m pip install psycopg2-binary', file=sys.stderr)
        return 1
    password = os.getenv('PGPASSWORD') or getpass.getpass('PostgreSQL password (hidden): ')
    connection = None
    try:
        connection = psycopg2.connect(host=args.host,port=args.port,dbname=args.database,user=args.user,
                                     password=password,sslmode=args.sslmode,connect_timeout=15)
        with connection:
            with connection.cursor() as cursor:
                cursor.execute("SET LOCAL lock_timeout = '15s'")
                cursor.execute("SET LOCAL statement_timeout = '120s'")
                cursor.execute('SELECT pg_advisory_xact_lock(72819432)')
                cursor.execute(SCHEMA_SQL)
                cursor.execute('SELECT account_enabled,auth_version,birth_date FROM users LIMIT 0')
                cursor.execute('SELECT bucket FROM login_limits LIMIT 0')
        print('Migration completed. Existing employee records retained; no employee data imported.')
        print('Configure the Python application with PGHOST, PGPORT, PGDATABASE, PGUSER and PGPASSWORD, then restart.')
        return 0
    except Exception as error:
        # Deliberately avoid printing connection strings or credentials.
        print('Migration failed; transaction rolled back. Error type: '+type(error).__name__+
              '; SQLSTATE: '+str(getattr(error,'pgcode',None) or 'connection/configuration'), file=sys.stderr)
        print('Check host/port, database permissions, backup and duplicate usernames/NIC before retrying.', file=sys.stderr)
        return 1
    finally:
        if connection is not None:
            connection.close()


if __name__ == '__main__':
    sys.exit(main())