USE expenses;

CREATE TABLE IF NOT EXISTS expense_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    report_title VARCHAR(150) NOT NULL DEFAULT 'Untitled Report',
    report_status ENUM('draft', 'unsubmitted', 'awaiting_approval', 'awaiting_reimbursement', 'approved', 'rejected') NOT NULL DEFAULT 'draft',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    currency_code VARCHAR(12) NOT NULL DEFAULT 'INR',
    submitted_at DATETIME NULL DEFAULT NULL,
    approved_at DATETIME NULL DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_expense_reports_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_expense_reports_user_status (user_id, report_status)
);

CREATE TABLE IF NOT EXISTS expense_report_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    report_id INT NULL DEFAULT NULL,
    expense_title VARCHAR(150) NOT NULL DEFAULT 'Expense',
    merchant_name VARCHAR(150) NOT NULL DEFAULT '',
    approval_status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    expense_date DATE NULL DEFAULT NULL,
    receipt_path VARCHAR(255) NOT NULL DEFAULT '',
    notes VARCHAR(500) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_expense_items_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_expense_items_report FOREIGN KEY (report_id) REFERENCES expense_reports(id) ON DELETE SET NULL,
    INDEX idx_expense_items_user_report (user_id, report_id),
    INDEX idx_expense_items_user_status (user_id, approval_status)
);

CREATE TABLE IF NOT EXISTS expense_advances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    report_id INT NULL DEFAULT NULL,
    advance_title VARCHAR(150) NOT NULL DEFAULT 'Advance',
    advance_status ENUM('pending', 'settled', 'cancelled') NOT NULL DEFAULT 'pending',
    amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    requested_on DATE NULL DEFAULT NULL,
    settled_on DATE NULL DEFAULT NULL,
    notes VARCHAR(500) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_expense_advances_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_expense_advances_report FOREIGN KEY (report_id) REFERENCES expense_reports(id) ON DELETE SET NULL,
    INDEX idx_expense_advances_user_report (user_id, report_id),
    INDEX idx_expense_advances_user_status (user_id, advance_status)
);

CREATE TABLE IF NOT EXISTS expense_trips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_name VARCHAR(150) NOT NULL DEFAULT 'Business Trip',
    destination VARCHAR(150) NOT NULL DEFAULT '',
    status ENUM('planned', 'active', 'completed', 'archived') NOT NULL DEFAULT 'planned',
    start_date DATE NULL DEFAULT NULL,
    end_date DATE NULL DEFAULT NULL,
    purpose VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_expense_trips_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_expense_trips_user_date (user_id, start_date, end_date)
);

CREATE OR REPLACE VIEW vw_dashboard_overview AS
SELECT
    u.id AS user_id,
    COALESCE(pending.pending_approval_count, 0) AS pending_approval_count,
    COALESCE(unreported.unreported_expenses_count, 0) AS unreported_expenses_count,
    COALESCE(adv.unreported_advances_count, 0) AS unreported_advances_count,
    COALESCE(adv.unreported_advances_amount, 0.00) AS unreported_advances_amount,
    COALESCE(rpt.unsubmitted_reports_count, 0) AS unsubmitted_reports_count,
    COALESCE(rpt.awaiting_approval_reports_count, 0) AS awaiting_approval_reports_count,
    COALESCE(rpt.awaiting_reimbursement_reports_count, 0) AS awaiting_reimbursement_reports_count,
    COALESCE(rpt.awaiting_reimbursement_amount, 0.00) AS awaiting_reimbursement_amount
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS pending_approval_count
    FROM expense_report_items
    WHERE approval_status = 'pending'
    GROUP BY user_id
) pending ON pending.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS unreported_expenses_count
    FROM expense_report_items
    WHERE report_id IS NULL
    GROUP BY user_id
) unreported ON unreported.user_id = u.id
LEFT JOIN (
    SELECT
        user_id,
        COUNT(*) AS unreported_advances_count,
        SUM(amount) AS unreported_advances_amount
    FROM expense_advances
    WHERE report_id IS NULL
    GROUP BY user_id
) adv ON adv.user_id = u.id
LEFT JOIN (
    SELECT
        user_id,
        SUM(CASE WHEN report_status = 'unsubmitted' THEN 1 ELSE 0 END) AS unsubmitted_reports_count,
        SUM(CASE WHEN report_status = 'awaiting_approval' THEN 1 ELSE 0 END) AS awaiting_approval_reports_count,
        SUM(CASE WHEN report_status = 'awaiting_reimbursement' THEN 1 ELSE 0 END) AS awaiting_reimbursement_reports_count,
        SUM(CASE WHEN report_status = 'awaiting_reimbursement' THEN total_amount ELSE 0 END) AS awaiting_reimbursement_amount
    FROM expense_reports
    GROUP BY user_id
) rpt ON rpt.user_id = u.id;

CREATE OR REPLACE VIEW vw_approvals_overview AS
SELECT
    u.id AS user_id,
    COALESCE(trips.pending_trip_count, 0) AS pending_trip_count,
    COALESCE(reports.pending_report_count, 0) AS pending_report_count,
    COALESCE(reports.awaiting_reimbursement_count, 0) AS awaiting_reimbursement_count,
    COALESCE(reports.awaiting_reimbursement_amount, 0.00) AS awaiting_reimbursement_amount
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS pending_trip_count
    FROM expense_trips
    WHERE status = 'planned'
    GROUP BY user_id
) trips ON trips.user_id = u.id
LEFT JOIN (
    SELECT
        user_id,
        COUNT(CASE WHEN report_status = 'awaiting_approval' THEN 1 END) AS pending_report_count,
        COUNT(CASE WHEN report_status = 'awaiting_reimbursement' THEN 1 END) AS awaiting_reimbursement_count,
        SUM(CASE WHEN report_status = 'awaiting_reimbursement' THEN total_amount ELSE 0 END) AS awaiting_reimbursement_amount
    FROM expense_reports
    GROUP BY user_id
) reports ON reports.user_id = u.id;

CREATE OR REPLACE VIEW vw_approvals_pending_trips AS
SELECT
    t.id,
    t.user_id,
    COALESCE(u.business_name, '') AS submitter_name,
    COALESCE(u.email, '') AS submitter_email,
    CONCAT('TR-', LPAD(t.id, 4, '0')) AS trip_number,
    COALESCE(t.trip_name, '') AS trip_details,
    COALESCE(t.destination, '') AS destination,
    COALESCE(t.status, 'planned') AS status,
    t.start_date,
    t.end_date,
    t.created_at
FROM expense_trips t
JOIN users u ON u.id = t.user_id
WHERE t.status = 'planned';

CREATE OR REPLACE VIEW vw_approvals_pending_reports AS
SELECT
    r.id,
    r.user_id,
    COALESCE(u.business_name, '') AS submitter_name,
    COALESCE(u.email, '') AS submitter_email,
    CONCAT('RP-', LPAD(r.id, 4, '0')) AS report_number,
    COALESCE(r.report_title, '') AS report_name,
    COALESCE(r.report_status, 'awaiting_approval') AS status,
    COALESCE(r.total_amount, 0.00) AS total_amount,
    COALESCE(r.currency_code, 'INR') AS currency_code,
    r.submitted_at,
    r.created_at,
    CASE
        WHEN r.report_status = 'awaiting_reimbursement' THEN COALESCE(r.total_amount, 0.00)
        ELSE 0.00
    END AS to_be_reimbursed
FROM expense_reports r
JOIN users u ON u.id = r.user_id
WHERE r.report_status IN ('awaiting_approval', 'awaiting_reimbursement');

CREATE OR REPLACE VIEW vw_analytics_overview AS
SELECT
    u.id AS user_id,
    COALESCE(exp.total_expense_count, 0) AS total_expense_count,
    COALESCE(exp.total_expense_amount, 0.00) AS total_expense_amount,
    COALESCE(exp.total_category_count, 0) AS total_category_count,
    COALESCE(rpt.total_report_count, 0) AS total_report_count,
    COALESCE(trp.total_trip_count, 0) AS total_trip_count,
    COALESCE(trp.active_trip_count, 0) AS active_trip_count,
    COALESCE(pending.pending_approval_count, 0) AS pending_approval_count,
    COALESCE(rpt.awaiting_approval_reports_count, 0) AS awaiting_approval_reports_count,
    COALESCE(rpt.awaiting_reimbursement_reports_count, 0) AS awaiting_reimbursement_reports_count,
    COALESCE(rpt.awaiting_reimbursement_amount, 0.00) AS awaiting_reimbursement_amount
FROM users u
LEFT JOIN (
    SELECT
        user_id,
        COUNT(*) AS total_expense_count,
        SUM(amount) AS total_expense_amount,
        COUNT(DISTINCT category_name) AS total_category_count
    FROM expense_report_items
    GROUP BY user_id
) exp ON exp.user_id = u.id
LEFT JOIN (
    SELECT
        user_id,
        COUNT(*) AS total_report_count,
        SUM(CASE WHEN report_status = 'awaiting_approval' THEN 1 ELSE 0 END) AS awaiting_approval_reports_count,
        SUM(CASE WHEN report_status = 'awaiting_reimbursement' THEN 1 ELSE 0 END) AS awaiting_reimbursement_reports_count,
        SUM(CASE WHEN report_status = 'awaiting_reimbursement' THEN total_amount ELSE 0 END) AS awaiting_reimbursement_amount
    FROM expense_reports
    GROUP BY user_id
) rpt ON rpt.user_id = u.id
LEFT JOIN (
    SELECT
        user_id,
        COUNT(*) AS total_trip_count,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_trip_count
    FROM expense_trips
    GROUP BY user_id
) trp ON trp.user_id = u.id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS pending_approval_count
    FROM expense_report_items
    WHERE approval_status = 'pending'
    GROUP BY user_id
) pending ON pending.user_id = u.id;

CREATE OR REPLACE VIEW vw_analytics_category_summary AS
SELECT
    eri.user_id,
    COALESCE(NULLIF(TRIM(eri.category_name), ''), 'Uncategorized') AS category_name,
    COUNT(*) AS expense_count,
    SUM(COALESCE(eri.amount, 0.00)) AS total_amount
FROM expense_report_items eri
GROUP BY eri.user_id, COALESCE(NULLIF(TRIM(eri.category_name), ''), 'Uncategorized');

CREATE OR REPLACE VIEW vw_analytics_merchant_summary AS
SELECT
    eri.user_id,
    COALESCE(NULLIF(TRIM(eri.merchant_name), ''), 'Unknown Merchant') AS merchant_name,
    COUNT(*) AS expense_count,
    SUM(COALESCE(eri.amount, 0.00)) AS total_amount
FROM expense_report_items eri
GROUP BY eri.user_id, COALESCE(NULLIF(TRIM(eri.merchant_name), ''), 'Unknown Merchant');

CREATE OR REPLACE VIEW vw_analytics_trip_summary AS
SELECT
    t.user_id,
    t.id AS trip_id,
    COALESCE(NULLIF(TRIM(t.trip_name), ''), 'Business Trip') AS trip_name,
    COALESCE(NULLIF(TRIM(t.destination), ''), 'Destination pending') AS destination,
    COALESCE(t.status, 'planned') AS status,
    t.start_date,
    t.end_date,
    t.created_at
FROM expense_trips t;

CREATE OR REPLACE VIEW vw_analytics_activity_feed AS
SELECT
    activity.user_id,
    activity.activity_type,
    activity.activity_label,
    activity.activity_status,
    activity.activity_amount,
    activity.activity_date,
    activity.source_id,
    activity.created_at
FROM (
    SELECT
        er.user_id,
        'report' AS activity_type,
        COALESCE(NULLIF(TRIM(er.report_title), ''), 'Expense Report') AS activity_label,
        COALESCE(er.report_status, 'draft') AS activity_status,
        COALESCE(er.total_amount, 0.00) AS activity_amount,
        COALESCE(er.submitted_at, er.created_at) AS activity_date,
        er.id AS source_id,
        er.created_at
    FROM expense_reports er
    UNION ALL
    SELECT
        t.user_id,
        'trip' AS activity_type,
        COALESCE(NULLIF(TRIM(t.trip_name), ''), 'Business Trip') AS activity_label,
        COALESCE(t.status, 'planned') AS activity_status,
        0.00 AS activity_amount,
        COALESCE(t.start_date, DATE(t.created_at)) AS activity_date,
        t.id AS source_id,
        t.created_at
    FROM expense_trips t
    UNION ALL
    SELECT
        a.user_id,
        'advance' AS activity_type,
        COALESCE(NULLIF(TRIM(a.advance_title), ''), 'Advance') AS activity_label,
        COALESCE(a.advance_status, 'pending') AS activity_status,
        COALESCE(a.amount, 0.00) AS activity_amount,
        COALESCE(a.advance_date, DATE(a.created_at)) AS activity_date,
        a.id AS source_id,
        a.created_at
    FROM expense_advances a
    UNION ALL
    SELECT
        eri.user_id,
        'expense' AS activity_type,
        COALESCE(NULLIF(TRIM(eri.expense_title), ''), 'Expense') AS activity_label,
        COALESCE(eri.approval_status, 'pending') AS activity_status,
        COALESCE(eri.amount, 0.00) AS activity_amount,
        COALESCE(eri.expense_date, DATE(eri.created_at)) AS activity_date,
        eri.id AS source_id,
        eri.created_at
    FROM expense_report_items eri
) activity;
