-- ============================================================================
-- ROA DATABASE VERIFICATION SCRIPT
-- ============================================================================
-- Comprehensive verification of all ROA database objects and configuration
--
-- Usage:
--   @verify-objects.sql
--
-- Connect as: SYSDBA or SYSTEM
--
-- This script verifies:
--   1. Tablespace ROA exists
--   2. Users CONTAFIN_ORACLE and company schemas exist
--   3. SYS custom objects (AUTH_PACK, NEWSCHEMA, etc.)
--   4. Public synonyms count and status
--   5. Invalid objects
--   6. Profile settings
--   7. Network ACL configuration
-- ============================================================================

SET ECHO OFF
SET FEEDBACK ON
SET SERVEROUTPUT ON
SET LINESIZE 200
SET PAGESIZE 100
WHENEVER SQLERROR CONTINUE

PROMPT
PROMPT ============================================================
PROMPT ROA DATABASE VERIFICATION REPORT
PROMPT Generated:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS "Report Time" FROM DUAL;
PROMPT ============================================================
PROMPT

-- ============================================================================
-- SECTION 1: DATABASE INFORMATION
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [1/10] DATABASE INFORMATION
PROMPT ========================================
PROMPT

COLUMN name FORMAT A20
COLUMN value FORMAT A50

SELECT name, cdb, open_mode, log_mode
FROM v$database;

PROMPT
PROMPT Instance Information:
SELECT instance_name, host_name, version_full, status
FROM v$instance;

PROMPT
PROMPT Current Container (for Oracle 12c+):
SHOW CON_NAME

-- ============================================================================
-- SECTION 2: TABLESPACE VERIFICATION
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [2/10] TABLESPACE ROA VERIFICATION
PROMPT ========================================
PROMPT

COLUMN tablespace_name FORMAT A15
COLUMN status FORMAT A10
COLUMN contents FORMAT A10
COLUMN extent_management FORMAT A10

SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces
WHERE tablespace_name = 'ROA';

PROMPT
PROMPT ROA Datafile(s):
COLUMN file_name FORMAT A60
COLUMN size_mb FORMAT 999,999.99
COLUMN max_mb FORMAT 999,999.99

SELECT file_name,
       bytes/1024/1024 AS size_mb,
       autoextensible,
       CASE WHEN maxbytes = 0 THEN bytes/1024/1024
            ELSE maxbytes/1024/1024
       END AS max_mb
FROM dba_data_files
WHERE tablespace_name = 'ROA';

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM dba_tablespaces WHERE tablespace_name = 'ROA';
    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('*** ERROR: Tablespace ROA does not exist! ***');
    ELSE
        DBMS_OUTPUT.PUT_LINE('OK: Tablespace ROA exists.');
    END IF;
END;
/

-- ============================================================================
-- SECTION 3: USER VERIFICATION
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [3/10] USER VERIFICATION
PROMPT ========================================
PROMPT

COLUMN username FORMAT A25
COLUMN default_tablespace FORMAT A15
COLUMN account_status FORMAT A20

PROMPT CONTAFIN_ORACLE user:
SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
WHERE username = 'CONTAFIN_ORACLE';

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM dba_users WHERE username = 'CONTAFIN_ORACLE';
    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('*** ERROR: User CONTAFIN_ORACLE does not exist! ***');
    ELSE
        DBMS_OUTPUT.PUT_LINE('OK: User CONTAFIN_ORACLE exists.');
    END IF;
END;
/

PROMPT
PROMPT Company schemas (users with ROA default tablespace):
SELECT username, account_status, created
FROM dba_users
WHERE default_tablespace = 'ROA'
  AND username NOT IN ('CONTAFIN_ORACLE')
ORDER BY username;

PROMPT
PROMPT CONTAFIN_ORACLE system privileges:
SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'CONTAFIN_ORACLE'
ORDER BY privilege;

PROMPT
PROMPT CONTAFIN_ORACLE roles:
SELECT granted_role, default_role
FROM dba_role_privs
WHERE grantee = 'CONTAFIN_ORACLE';

-- ============================================================================
-- SECTION 4: SYS CUSTOM OBJECTS VERIFICATION
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [4/10] SYS CUSTOM OBJECTS
PROMPT ========================================
PROMPT

COLUMN object_name FORMAT A25
COLUMN object_type FORMAT A15
COLUMN status FORMAT A10

PROMPT Expected SYS custom objects:
SELECT object_name, object_type, status, created
FROM dba_objects
WHERE owner = 'SYS'
  AND object_name IN (
      'AUTH_PACK',
      'AUTH_DETALII',
      'AUTH_SERII',
      'VAUTH_SERII',
      'SEQ_AUTH_SERII',
      'EXECUTESCRIPTOS',
      'NEWSCHEMA',
      'NEWSCHEMAJOB',
      'UPDATESQLPLUS',
      'PINFO',
      'INFO'
  )
ORDER BY object_type, object_name;

DECLARE
    v_count NUMBER;
    v_expected NUMBER := 12; -- Tables: 3, View: 1, Sequence: 1, Package: 2, Procedures: 5
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM dba_objects
    WHERE owner = 'SYS'
      AND object_name IN (
          'AUTH_PACK', 'AUTH_DETALII', 'AUTH_SERII', 'VAUTH_SERII',
          'SEQ_AUTH_SERII', 'EXECUTESCRIPTOS', 'NEWSCHEMA', 'NEWSCHEMAJOB',
          'UPDATESQLPLUS', 'PINFO', 'INFO'
      )
      AND status = 'VALID';

    DBMS_OUTPUT.PUT_LINE('Found ' || v_count || ' valid SYS custom objects.');
    IF v_count < 10 THEN
        DBMS_OUTPUT.PUT_LINE('*** WARNING: Some SYS objects may be missing or invalid! ***');
    ELSE
        DBMS_OUTPUT.PUT_LINE('OK: All expected SYS custom objects are present.');
    END IF;
END;
/

-- ============================================================================
-- SECTION 5: PUBLIC SYNONYMS VERIFICATION
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [5/10] PUBLIC SYNONYMS
PROMPT ========================================
PROMPT

PROMPT Public synonyms pointing to CONTAFIN_ORACLE:
SELECT COUNT(*) AS total_synonyms
FROM dba_synonyms
WHERE owner = 'PUBLIC'
  AND table_owner = 'CONTAFIN_ORACLE';

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM dba_synonyms
    WHERE owner = 'PUBLIC'
      AND table_owner = 'CONTAFIN_ORACLE';

    IF v_count < 50 THEN
        DBMS_OUTPUT.PUT_LINE('*** WARNING: Expected 50+ public synonyms, found ' || v_count || ' ***');
    ELSE
        DBMS_OUTPUT.PUT_LINE('OK: Found ' || v_count || ' public synonyms for CONTAFIN_ORACLE.');
    END IF;
END;
/

PROMPT
PROMPT Key public synonyms check:
SELECT
    CASE WHEN EXISTS (SELECT 1 FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name='SYN_NOM_FIRME')
         THEN 'OK' ELSE 'MISSING' END AS SYN_NOM_FIRME,
    CASE WHEN EXISTS (SELECT 1 FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name='SYN_NOM_PROGRAME')
         THEN 'OK' ELSE 'MISSING' END AS SYN_NOM_PROGRAME,
    CASE WHEN EXISTS (SELECT 1 FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name='SYN_UTILIZATORI')
         THEN 'OK' ELSE 'MISSING' END AS SYN_UTILIZATORI,
    CASE WHEN EXISTS (SELECT 1 FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name='PACK_UPDATE')
         THEN 'OK' ELSE 'MISSING' END AS PACK_UPDATE,
    CASE WHEN EXISTS (SELECT 1 FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name='PACK_UTILS')
         THEN 'OK' ELSE 'MISSING' END AS PACK_UTILS
FROM DUAL;

-- ============================================================================
-- SECTION 6: CONTAFIN_ORACLE OBJECTS
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [6/10] CONTAFIN_ORACLE SCHEMA OBJECTS
PROMPT ========================================
PROMPT

PROMPT Object count by type:
SELECT object_type, COUNT(*) AS count
FROM dba_objects
WHERE owner = 'CONTAFIN_ORACLE'
GROUP BY object_type
ORDER BY object_type;

PROMPT
PROMPT Total objects in CONTAFIN_ORACLE:
SELECT COUNT(*) AS total_objects
FROM dba_objects
WHERE owner = 'CONTAFIN_ORACLE';

-- ============================================================================
-- SECTION 7: INVALID OBJECTS
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [7/10] INVALID OBJECTS CHECK
PROMPT ========================================
PROMPT

PROMPT Invalid SYS custom objects:
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'SYS'
  AND object_name IN (
      'AUTH_PACK', 'AUTH_DETALII', 'AUTH_SERII', 'VAUTH_SERII',
      'SEQ_AUTH_SERII', 'EXECUTESCRIPTOS', 'NEWSCHEMA', 'NEWSCHEMAJOB',
      'UPDATESQLPLUS', 'PINFO', 'INFO'
  )
  AND status != 'VALID';

PROMPT
PROMPT Invalid CONTAFIN_ORACLE objects:
SELECT object_name, object_type
FROM dba_objects
WHERE owner = 'CONTAFIN_ORACLE'
  AND status = 'INVALID'
ORDER BY object_type, object_name;

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM dba_objects
    WHERE owner = 'CONTAFIN_ORACLE'
      AND status = 'INVALID';

    IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('*** WARNING: ' || v_count || ' invalid objects in CONTAFIN_ORACLE ***');
        DBMS_OUTPUT.PUT_LINE('Run: ALTER PACKAGE/PROCEDURE/VIEW ... COMPILE; to fix');
    ELSE
        DBMS_OUTPUT.PUT_LINE('OK: No invalid objects in CONTAFIN_ORACLE.');
    END IF;
END;
/

-- ============================================================================
-- SECTION 8: PROFILE SETTINGS
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [8/10] PROFILE PASSWORD SETTINGS
PROMPT ========================================
PROMPT

COLUMN resource_name FORMAT A30
COLUMN limit FORMAT A20

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
  AND resource_type = 'PASSWORD'
ORDER BY resource_name;

DECLARE
    v_limit VARCHAR2(40);
BEGIN
    SELECT limit INTO v_limit
    FROM dba_profiles
    WHERE profile = 'DEFAULT'
      AND resource_name = 'PASSWORD_LIFE_TIME';

    IF v_limit != 'UNLIMITED' THEN
        DBMS_OUTPUT.PUT_LINE('*** WARNING: PASSWORD_LIFE_TIME is not UNLIMITED (' || v_limit || ')');
        DBMS_OUTPUT.PUT_LINE('Run configure-profile.sql to fix.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('OK: PASSWORD_LIFE_TIME is UNLIMITED.');
    END IF;
END;
/

-- ============================================================================
-- SECTION 9: DIRECTORY AND ACL VERIFICATION
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [9/10] DIRECTORY AND ACL CONFIGURATION
PROMPT ========================================
PROMPT

PROMPT DMPDIR Directory:
COLUMN directory_name FORMAT A15
COLUMN directory_path FORMAT A60

SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = 'DMPDIR';

PROMPT
PROMPT Network ACL for CONTAFIN_ORACLE:
SELECT host, acl
FROM dba_network_acls
WHERE acl LIKE '%roaupdate%';

PROMPT
PROMPT ACL Privileges:
SELECT principal, privilege, is_grant
FROM dba_network_acl_privileges
WHERE acl LIKE '%roaupdate%';

-- ============================================================================
-- SECTION 10: AUTH_DETALII DATA CHECK
-- ============================================================================

PROMPT
PROMPT ========================================
PROMPT [10/10] LICENSE DATA CHECK
PROMPT ========================================
PROMPT

PROMPT AUTH_DETALII (Client ID):
SELECT * FROM SYS.AUTH_DETALII;

PROMPT
PROMPT AUTH_SERII (License serials - without decryption):
SELECT id_serie, id_program, sters, dataora, id_util
FROM SYS.AUTH_SERII
WHERE sters = 0
ORDER BY id_program;

PROMPT
PROMPT License View (via VAUTH_SERII):
BEGIN
    FOR rec IN (SELECT * FROM SYS.VAUTH_SERII) LOOP
        DBMS_OUTPUT.PUT_LINE('Program: ' || rec.nume ||
                             ', Users: ' || rec.nr_util ||
                             ', Valid until: ' || TO_CHAR(rec.data_val, 'YYYY-MM-DD'));
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Note: Cannot query VAUTH_SERII - ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('This is normal if synonyms are not yet created.');
END;
/

-- ============================================================================
-- SUMMARY
-- ============================================================================

PROMPT
PROMPT ============================================================
PROMPT VERIFICATION SUMMARY
PROMPT ============================================================
PROMPT

DECLARE
    v_errors NUMBER := 0;
    v_warnings NUMBER := 0;
    v_count NUMBER;
    v_limit VARCHAR2(40);
BEGIN
    -- Check tablespace
    SELECT COUNT(*) INTO v_count FROM dba_tablespaces WHERE tablespace_name = 'ROA';
    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Tablespace ROA missing');
        v_errors := v_errors + 1;
    END IF;

    -- Check CONTAFIN_ORACLE user
    SELECT COUNT(*) INTO v_count FROM dba_users WHERE username = 'CONTAFIN_ORACLE';
    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: User CONTAFIN_ORACLE missing');
        v_errors := v_errors + 1;
    END IF;

    -- Check AUTH_PACK
    SELECT COUNT(*) INTO v_count FROM dba_objects
    WHERE owner = 'SYS' AND object_name = 'AUTH_PACK' AND object_type = 'PACKAGE BODY' AND status = 'VALID';
    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: AUTH_PACK package missing or invalid');
        v_errors := v_errors + 1;
    END IF;

    -- Check public synonyms
    SELECT COUNT(*) INTO v_count FROM dba_synonyms
    WHERE owner = 'PUBLIC' AND table_owner = 'CONTAFIN_ORACLE';
    IF v_count < 50 THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: Only ' || v_count || ' public synonyms (expected 50+)');
        v_warnings := v_warnings + 1;
    END IF;

    -- Check invalid objects
    SELECT COUNT(*) INTO v_count FROM dba_objects
    WHERE owner = 'CONTAFIN_ORACLE' AND status = 'INVALID';
    IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_count || ' invalid objects in CONTAFIN_ORACLE');
        v_warnings := v_warnings + 1;
    END IF;

    -- Check profile
    SELECT limit INTO v_limit FROM dba_profiles
    WHERE profile = 'DEFAULT' AND resource_name = 'PASSWORD_LIFE_TIME';
    IF v_limit != 'UNLIMITED' THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: Password expiration is enabled');
        v_warnings := v_warnings + 1;
    END IF;

    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Total Errors:   ' || v_errors);
    DBMS_OUTPUT.PUT_LINE('Total Warnings: ' || v_warnings);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    IF v_errors = 0 AND v_warnings = 0 THEN
        DBMS_OUTPUT.PUT_LINE('STATUS: ALL CHECKS PASSED');
    ELSIF v_errors = 0 THEN
        DBMS_OUTPUT.PUT_LINE('STATUS: PASSED WITH WARNINGS');
    ELSE
        DBMS_OUTPUT.PUT_LINE('STATUS: FAILED - Please fix errors');
    END IF;
END;
/

PROMPT
PROMPT ============================================================
PROMPT END OF VERIFICATION REPORT
PROMPT ============================================================
PROMPT