\set ON_ERROR_STOP on
\timing on

-- The CSV directory is passed as a psql variable:  psql -v csv_dir=/path/to/seed_data -f load_seed.sql
-- \copy does not expand variables in its arguments, so psql changes into the directory instead.
\cd :csv_dir

-- Each BEGIN/COMMIT block commits on its own. After a failure, fix the cause,
-- resume at the failed block and run every block after it, including the
-- sequence resets; do not repeat the blocks that already committed.

-- =============================================================================
-- 1. LOOKUP TABLES
-- =============================================================================

\echo 'Loading lookup tables...'

BEGIN;
\copy Role (role_id, role_name, description) FROM 'role.csv' CSV HEADER;
\copy Permission (permission_id, action_name) FROM 'permission.csv' CSV HEADER;
\copy Industry (industry_id, industry_name) FROM 'industry.csv' CSV HEADER;
\copy Project_Status (status_id, status_name) FROM 'project_status.csv' CSV HEADER;
\copy Technology (technology_id, technology_name) FROM 'technology.csv' CSV HEADER;
\copy Rating_Dimension (dimension_id, dimension_name, description) FROM 'rating_dimension.csv' CSV HEADER;
\copy Subscription_Tier (tier_id, tier_name, list_price, allows_custom_pricing) FROM 'subscription_tier.csv' CSV HEADER;
\copy Role_Permission (role_id, permission_id) FROM 'role_permission.csv' CSV HEADER;
COMMIT;

-- =============================================================================
-- 2. CORE ENTITIES
-- =============================================================================

\echo 'Loading vendors and clients...'

BEGIN;
\copy Vendor (vendor_id, agency_name, website) FROM 'vendor.csv' CSV HEADER;
\copy Client (client_id, industry_id, company_name, contact_email) FROM 'client.csv' CSV HEADER;
COMMIT;

\echo 'Loading users...'

BEGIN;
\copy "User" (user_id, type, first_name, last_name, email, password_hash, is_active, last_login_at, created_at, updated_at) FROM 'user.csv' CSV HEADER;
\copy Client_User (user_id, client_id) FROM 'client_user.csv' CSV HEADER;
\copy Vendor_User (user_id, vendor_id) FROM 'vendor_user.csv' CSV HEADER;
\copy Management_User (user_id, role_id) FROM 'management_user.csv' CSV HEADER;
COMMIT;

-- =============================================================================
-- 3. SUBSCRIPTIONS & CONTRACTS
-- =============================================================================

\echo 'Loading subscriptions...'

BEGIN;
\copy Vendor_Subscription (contract_id, vendor_id, tier_id, negotiated_price, start_date, end_date, is_active, created_at, updated_at) FROM 'vendor_subscription.csv' CSV HEADER;
COMMIT;

\echo 'Loading contracts...'

BEGIN;
\copy Client_Vendor_Contract (contract_id, client_id, vendor_id, contract_number, contract_title, start_date, end_date, total_value, currency_code, terms_summary, is_active, created_at, updated_at) FROM 'client_vendor_contract.csv' CSV HEADER;
COMMIT;

-- =============================================================================
-- 4. PROJECTS
-- =============================================================================

\echo 'Loading projects...'

BEGIN;
\copy Project (project_id, contract_id, status_id, project_name, start_date, end_date, budget, created_at, updated_at) FROM 'project.csv' CSV HEADER;
COMMIT;

-- =============================================================================
-- 5. PROJECT CHILD TABLES
-- =============================================================================

\echo 'Loading project_technology...'

BEGIN;
\copy Project_Technology (project_id, technology_id) FROM 'project_technology.csv' CSV HEADER;
COMMIT;

\echo 'Loading project_budget_audit...'

BEGIN;
\copy Project_Budget_Audit (audit_id, project_id, old_budget, new_budget, created_at, updated_at) FROM 'project_budget_audit.csv' CSV HEADER;
COMMIT;

\echo 'Loading project_status_history...'

BEGIN;
\copy Project_Status_History (project_status_history_id, project_id, vendor_user_id, management_user_id, status_id, changed_at, comment) FROM 'project_status_history.csv' CSV HEADER;
COMMIT;

-- =============================================================================
-- 6. REVIEWS
-- =============================================================================

\echo 'Loading reviews...'

BEGIN;
\copy Review (review_id, project_id, client_user_id, review_date, summary_text, is_published, created_at, updated_at) FROM 'review.csv' CSV HEADER;
COMMIT;

\echo 'Loading review_score...'

BEGIN;
\copy Review_Score (review_id, dimension_id, score_value) FROM 'review_score.csv' CSV HEADER;
COMMIT;

\echo 'Loading dispute_ticket...'

BEGIN;
\copy Dispute_Ticket (ticket_id, assigned_management_user_id, review_id, vendor_user_id, reason, is_resolved, filed_at, resolved_at, resolution_note, created_at, updated_at) FROM 'dispute_ticket.csv' CSV HEADER;
COMMIT;

-- =============================================================================
-- SEQUENCE RESETS
-- =============================================================================

\echo 'Resetting sequences...'

BEGIN;
SELECT setval(pg_get_serial_sequence('Role',             'role_id'),
              (SELECT MAX(role_id)          FROM Role));
SELECT setval(pg_get_serial_sequence('Permission',       'permission_id'),
              (SELECT MAX(permission_id)    FROM Permission));
SELECT setval(pg_get_serial_sequence('Industry',         'industry_id'),
              (SELECT MAX(industry_id)      FROM Industry));
SELECT setval(pg_get_serial_sequence('Project_Status',   'status_id'),
              (SELECT MAX(status_id)        FROM Project_Status));
SELECT setval(pg_get_serial_sequence('Technology',       'technology_id'),
              (SELECT MAX(technology_id)    FROM Technology));
SELECT setval(pg_get_serial_sequence('Rating_Dimension', 'dimension_id'),
              (SELECT MAX(dimension_id)     FROM Rating_Dimension));
SELECT setval(pg_get_serial_sequence('Subscription_Tier','tier_id'),
              (SELECT MAX(tier_id)          FROM Subscription_Tier));
SELECT setval(pg_get_serial_sequence('Vendor',           'vendor_id'),
              (SELECT MAX(vendor_id)        FROM Vendor));
SELECT setval(pg_get_serial_sequence('Client',           'client_id'),
              (SELECT MAX(client_id)        FROM Client));
SELECT setval(pg_get_serial_sequence('"User"',           'user_id'),
              (SELECT MAX(user_id)          FROM "User"));
SELECT setval(pg_get_serial_sequence('Vendor_Subscription',    'contract_id'),
              (SELECT MAX(contract_id)      FROM Vendor_Subscription));
SELECT setval(pg_get_serial_sequence('Client_Vendor_Contract', 'contract_id'),
              (SELECT MAX(contract_id)      FROM Client_Vendor_Contract));
SELECT setval(pg_get_serial_sequence('Project',          'project_id'),
              (SELECT MAX(project_id)       FROM Project));
SELECT setval(pg_get_serial_sequence('Project_Budget_Audit',   'audit_id'),
              (SELECT MAX(audit_id)         FROM Project_Budget_Audit));
SELECT setval(pg_get_serial_sequence('Project_Status_History',
                                     'project_status_history_id'),
              (SELECT MAX(project_status_history_id) FROM Project_Status_History));
SELECT setval(pg_get_serial_sequence('Review',           'review_id'),
              (SELECT MAX(review_id)        FROM Review));
SELECT setval(pg_get_serial_sequence('Dispute_Ticket',   'ticket_id'),
              (SELECT MAX(ticket_id)        FROM Dispute_Ticket));
COMMIT;

-- Refresh planner statistics after the bulk load.
SET statement_timeout = 0;
VACUUM (ANALYZE);

\echo 'Done.'