-- Import this after:
-- 1. selecting your database in phpMyAdmin
-- 2. importing phpmyadmin_settings_full.sql

-- Create one demo organization only if none exists yet.
INSERT INTO organizations
    (organisation_name, business_location, industry, address_line1, address_line2, state, city, pincode, payroll_run)
SELECT
    'demo',
    'India',
    'Technology',
    '123 Demo Street',
    'Suite 5A',
    'Tamil Nadu',
    'Chennai',
    '600001',
    'Monthly'
WHERE NOT EXISTS (
    SELECT 1 FROM organizations
);

SET @organization_id := (
    SELECT id
    FROM organizations
    ORDER BY id DESC
    LIMIT 1
);

SET @organization_id_char := CAST(@organization_id AS CHAR(50));

INSERT INTO organization_settings
    (organization_id, logo_path, date_format, field_separator, primary_contact_name, primary_contact_email, sender_email)
SELECT
    @organization_id,
    NULL,
    'dd/MM/yyyy',
    '/',
    'Demo Admin',
    'demo@example.com',
    'message-service@mail.zohopayroll.in'
WHERE NOT EXISTS (
    SELECT 1 FROM organization_settings WHERE organization_id = @organization_id
);

INSERT INTO branding_preferences
    (organization_id, appearance, accent_color)
SELECT
    @organization_id,
    'light',
    'blue'
WHERE NOT EXISTS (
    SELECT 1 FROM branding_preferences WHERE organization_id = @organization_id
);

INSERT INTO work_locations
    (organization_id, location_name, address_line1, address_line2, state, city, pincode, is_filing_address)
SELECT
    @organization_id,
    'Head Office',
    '123 Demo Street',
    'Suite 5A',
    'Tamil Nadu',
    'Chennai',
    '600001',
    1
WHERE NOT EXISTS (
    SELECT 1 FROM work_locations WHERE organization_id = @organization_id AND location_name = 'Head Office'
);

INSERT INTO departments
    (org_id, name, code, description)
SELECT @organization_id_char, 'Human Resources', 'HR', 'Handles recruitment and employee operations'
WHERE NOT EXISTS (
    SELECT 1 FROM departments WHERE org_id = @organization_id_char AND name = 'Human Resources'
);

INSERT INTO departments
    (org_id, name, code, description)
SELECT @organization_id_char, 'Finance', 'FIN', 'Handles payroll, accounting and statutory filings'
WHERE NOT EXISTS (
    SELECT 1 FROM departments WHERE org_id = @organization_id_char AND name = 'Finance'
);

INSERT INTO designations
    (organization_id, name)
SELECT @organization_id, 'Software Engineer'
WHERE NOT EXISTS (
    SELECT 1 FROM designations WHERE organization_id = @organization_id AND name = 'Software Engineer'
);

INSERT INTO designations
    (organization_id, name)
SELECT @organization_id, 'HR Manager'
WHERE NOT EXISTS (
    SELECT 1 FROM designations WHERE organization_id = @organization_id AND name = 'HR Manager'
);

INSERT INTO subscription_details
    (organization_id, plan_name, billing_cycle, monthly_price, employee_limit, status, trial_ends_on, feature_summary, premium_feature_summary)
SELECT
    @organization_id,
    '14-DAYS FREE TRIAL',
    'monthly',
    0.00,
    25,
    'trial',
    DATE_ADD(CURDATE(), INTERVAL 14 DAY),
    'Reporting Tags, Workflow Rules, Salary Templates',
    'Custom Buttons and Links'
WHERE NOT EXISTS (
    SELECT 1 FROM subscription_details WHERE organization_id = @organization_id
);

INSERT INTO settings_roles
    (organization_id, role_name, description, is_system, permissions_json)
SELECT
    @organization_id,
    'Admin',
    'Unrestricted access to all modules.',
    1,
    '{"all_access":true}'
WHERE NOT EXISTS (
    SELECT 1 FROM settings_roles WHERE organization_id = @organization_id AND role_name = 'Admin'
);

INSERT INTO settings_roles
    (organization_id, role_name, description, is_system, permissions_json)
SELECT
    @organization_id,
    'Manager',
    'Access to all modules except organisation settings.',
    1,
    '{"manage_modules":true,"organisation_settings":false}'
WHERE NOT EXISTS (
    SELECT 1 FROM settings_roles WHERE organization_id = @organization_id AND role_name = 'Manager'
);

INSERT INTO settings_users
    (organization_id, full_name, email, role_name, status, avatar_path)
SELECT
    @organization_id,
    'Demo Admin',
    'demo@example.com',
    'Admin',
    'ACTIVE',
    NULL
WHERE NOT EXISTS (
    SELECT 1 FROM settings_users WHERE organization_id = @organization_id AND email = 'demo@example.com'
);

INSERT INTO settings_users
    (organization_id, full_name, email, role_name, status, avatar_path)
SELECT
    @organization_id,
    'Priya Sharma',
    'priya.sharma@example.com',
    'Manager',
    'ACTIVE',
    NULL
WHERE NOT EXISTS (
    SELECT 1 FROM settings_users WHERE organization_id = @organization_id AND email = 'priya.sharma@example.com'
);

INSERT INTO pay_schedules
    (organization_id, pay_frequency, working_days_json, pay_day_label, first_pay_period, first_pay_date, working_days_count, upcoming_payrolls_json)
SELECT
    @organization_id,
    'Every month',
    '["Mon","Tue","Wed","Thu","Fri"]',
    '1st of every month',
    'May 2025',
    '2025-05-30',
    20,
    '[{"label":"May-2025","pay_date":"2025-05-30"},{"label":"June-2025","pay_date":"2025-07-01"}]'
WHERE NOT EXISTS (
    SELECT 1 FROM pay_schedules WHERE organization_id = @organization_id
);

INSERT INTO statutory_components
    (organization_id, component_code, is_enabled, config_json)
SELECT
    @organization_id,
    'pt',
    1,
    '{"enabled":true,"title":"Professional Tax","card":{"office":"Head Office","state":"Tamil Nadu","deduction_cycle":"Half Yearly"}}'
WHERE NOT EXISTS (
    SELECT 1 FROM statutory_components WHERE organization_id = @organization_id AND component_code = 'pt'
);

INSERT INTO direct_deposit_setups
    (organization_id, provider_code, status, customer_id, password_part_one, password_part_two, bank_account_label, bank_account_number, live_balance, registered_mobile, secret_pin, notes, last_synced_at)
SELECT
    @organization_id,
    'yes_bank',
    'active',
    'YESCUST001',
    'abc123',
    'xyz789',
    'Zoho Payroll - Bank Account',
    '123456789012',
    250000.50,
    '9876543210',
    '123456',
    'Demo YES Bank direct deposit setup',
    NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM direct_deposit_setups WHERE organization_id = @organization_id AND provider_code = 'yes_bank'
);

INSERT INTO zoho_app_integrations
    (organization_id, app_code, app_name, status, connection_name, connected_at)
SELECT
    @organization_id,
    'zoho_books',
    'Zoho Books',
    'inactive',
    NULL,
    NULL
WHERE NOT EXISTS (
    SELECT 1 FROM zoho_app_integrations WHERE organization_id = @organization_id AND app_code = 'zoho_books'
);

INSERT INTO developer_connections
    (organization_id, service_type, service_code, service_name, connection_name, scope_summary, status)
SELECT
    @organization_id,
    'default',
    'zoho_analytics',
    'Zoho Analytics',
    'Analytics Reporting Connection',
    'read reports, write payroll dashboards',
    'active'
WHERE NOT EXISTS (
    SELECT 1 FROM developer_connections WHERE organization_id = @organization_id AND connection_name = 'Analytics Reporting Connection'
);

INSERT INTO incoming_webhooks
    (organization_id, name, description, script_body, status)
SELECT
    @organization_id,
    'Employee Update Webhook',
    'Handles incoming employee update payloads',
    '//Demo webhook script\nresultMap = Map();\nresultMap.put("message","Success");\nresultMap.put("code",0);\nreturn resultMap;',
    'active'
WHERE NOT EXISTS (
    SELECT 1 FROM incoming_webhooks WHERE organization_id = @organization_id AND name = 'Employee Update Webhook'
);

INSERT INTO data_backups
    (organization_id, backup_type, requested_by, recipient_email, file_type, export_status, download_link)
SELECT
    @organization_id,
    'backup_data',
    'Demo Admin',
    'demo@example.com',
    'ZIP',
    'completed',
    'https://example.com/downloads/demo-backup.zip'
WHERE NOT EXISTS (
    SELECT 1 FROM data_backups WHERE organization_id = @organization_id AND backup_type = 'backup_data'
);

INSERT INTO data_backups
    (organization_id, backup_type, requested_by, recipient_email, file_type, export_status, download_link)
SELECT
    @organization_id,
    'audit_trail',
    'Demo Admin',
    'demo@example.com',
    'ZIP',
    'queued',
    NULL
WHERE NOT EXISTS (
    SELECT 1 FROM data_backups WHERE organization_id = @organization_id AND backup_type = 'audit_trail'
);

INSERT INTO support_requests
    (organization_id, subject, message, attachment_path, contact_number, criticality, created_by, created_email)
SELECT
    @organization_id,
    'Unable to process payroll for March',
    'The payroll approval flow is not completing after review. Please help us verify the setup.',
    NULL,
    '9876543210',
    'High',
    'Demo Admin',
    'demo@example.com'
WHERE NOT EXISTS (
    SELECT 1 FROM support_requests WHERE organization_id = @organization_id AND subject = 'Unable to process payroll for March'
);
