CREATE TABLE signup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    phone_code VARCHAR(10) NOT NULL DEFAULT '+91',
    password VARCHAR(255) NOT NULL,
    state VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    org_id VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE DATABASE IF NOT EXISTS expenses CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE expenses;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone_country_code VARCHAR(8) NOT NULL DEFAULT '+91',
    phone_number VARCHAR(20) NOT NULL,
    is_phone_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE otp_verifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    otp_code VARCHAR(10) NOT NULL,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_otp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE organizations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    organization_name VARCHAR(150) NOT NULL,
    organization_location VARCHAR(100) NOT NULL,
    state_union_territory VARCHAR(100) NOT NULL,
    industry VARCHAR(120) NOT NULL,
    base_currency VARCHAR(60) NOT NULL DEFAULT 'INR - Indian Rupee',
    language VARCHAR(40) NOT NULL DEFAULT 'English',
    time_zone VARCHAR(120) NOT NULL DEFAULT '(GMT 5:30) India Standard Time (Asia/Kolkata)',
    terms_accepted TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_org_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE organization_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    address_line1 VARCHAR(150) NULL,
    address_line2 VARCHAR(150) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    country VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_address_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

CREATE TABLE countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    phone_code VARCHAR(8) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE states (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_states_country FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
);

CREATE TABLE industries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE currencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE timezones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL UNIQUE,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE expense_trips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_name VARCHAR(150) NOT NULL DEFAULT 'Business Trip',
    travel_type ENUM('domestic', 'international') NOT NULL DEFAULT 'domestic',
    business_purpose VARCHAR(255) NOT NULL DEFAULT '',
    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,
    cover_image_path 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 TABLE expense_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_id INT NULL DEFAULT NULL,
    report_title VARCHAR(150) NOT NULL DEFAULT 'Untitled Report',
    business_purpose VARCHAR(500) NOT NULL DEFAULT '',
    start_date DATE NULL DEFAULT NULL,
    end_date DATE NULL DEFAULT NULL,
    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,
    CONSTRAINT fk_expense_reports_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(id) ON DELETE SET NULL,
    INDEX idx_expense_reports_user_status (user_id, report_status)
);

CREATE TABLE expense_report_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    report_id INT NULL DEFAULT NULL,
    trip_id INT NULL DEFAULT NULL,
    expense_title VARCHAR(150) NOT NULL DEFAULT 'Expense',
    merchant_name VARCHAR(150) NOT NULL DEFAULT '',
    category_name VARCHAR(120) NOT NULL DEFAULT '',
    approval_status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    currency_code VARCHAR(12) NOT NULL DEFAULT 'INR',
    expense_date DATE NULL DEFAULT NULL,
    claim_reimbursement TINYINT(1) NOT NULL DEFAULT 1,
    description VARCHAR(500) NOT NULL DEFAULT '',
    reference_no VARCHAR(100) NOT NULL DEFAULT '',
    receipt_path 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_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,
    CONSTRAINT fk_expense_items_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(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 expense_advances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_id INT NULL DEFAULT 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,
    advance_date DATE NULL DEFAULT NULL,
    paid_through VARCHAR(100) NOT NULL DEFAULT 'Petty Cash',
    reference_no VARCHAR(100) 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_advances_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_expense_advances_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_advances_report FOREIGN KEY (report_id) REFERENCES expense_reports(id) ON DELETE SET NULL,
    INDEX idx_expense_advances_user_status (user_id, advance_status)
);

CREATE TABLE card_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    card_type ENUM('corporate', 'personal') NOT NULL DEFAULT 'personal',
    card_network VARCHAR(50) NOT NULL DEFAULT '',
    bank_name VARCHAR(150) NOT NULL DEFAULT '',
    currency_code VARCHAR(12) NOT NULL DEFAULT 'INR',
    status ENUM('active', 'inactive', 'archived') NOT NULL DEFAULT 'active',
    transactions_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_card_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_card_accounts_user_type (user_id, card_type)
);

CREATE TABLE card_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    card_account_id INT NULL DEFAULT NULL,
    report_id INT NULL DEFAULT NULL,
    expense_item_id INT NULL DEFAULT NULL,
    transaction_date DATE NULL DEFAULT NULL,
    payee_name VARCHAR(150) NOT NULL DEFAULT '',
    details VARCHAR(255) NOT NULL DEFAULT '',
    card_account_name VARCHAR(150) NOT NULL DEFAULT '',
    amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    currency_code VARCHAR(12) NOT NULL DEFAULT 'INR',
    transaction_status ENUM('unmatched', 'matched', 'ignored') NOT NULL DEFAULT 'unmatched',
    reference_no VARCHAR(100) 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_card_transactions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_card_transactions_account FOREIGN KEY (card_account_id) REFERENCES card_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_card_transactions_report FOREIGN KEY (report_id) REFERENCES expense_reports(id) ON DELETE SET NULL,
    CONSTRAINT fk_card_transactions_item FOREIGN KEY (expense_item_id) REFERENCES expense_report_items(id) ON DELETE SET NULL,
    INDEX idx_card_transactions_user_status (user_id, transaction_status),
    INDEX idx_card_transactions_user_date (user_id, transaction_date)
);

CREATE TABLE card_statement_imports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    card_account_id INT NULL DEFAULT NULL,
    original_filename VARCHAR(255) NOT NULL DEFAULT '',
    stored_filename VARCHAR(255) NOT NULL DEFAULT '',
    file_type VARCHAR(30) NOT NULL DEFAULT '',
    import_status ENUM('uploaded', 'parsed', 'matched', 'failed') NOT NULL DEFAULT 'uploaded',
    imported_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_card_imports_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_card_imports_account FOREIGN KEY (card_account_id) REFERENCES card_accounts(id) ON DELETE SET NULL,
    INDEX idx_card_imports_user_status (user_id, import_status)
);

INSERT INTO countries (name, phone_code, sort_order) VALUES
('India', '+91', 1),
('United States', '+1', 2)
ON DUPLICATE KEY UPDATE phone_code = VALUES(phone_code), sort_order = VALUES(sort_order);

INSERT INTO states (country_id, name, sort_order)
SELECT c.id, v.name, v.sort_order
FROM countries c
JOIN (
    SELECT 'India' AS country_name, 'Andhra Pradesh' AS name, 1 AS sort_order UNION ALL
    SELECT 'India', 'Tamil Nadu', 2 UNION ALL
    SELECT 'India', 'Karnataka', 3 UNION ALL
    SELECT 'India', 'Kerala', 4 UNION ALL
    SELECT 'India', 'Maharashtra', 5 UNION ALL
    SELECT 'India', 'Delhi', 6 UNION ALL
    SELECT 'United States', 'California', 1 UNION ALL
    SELECT 'United States', 'Texas', 2 UNION ALL
    SELECT 'United States', 'New York', 3 UNION ALL
    SELECT 'United States', 'Florida', 4 UNION ALL
    SELECT 'United States', 'Illinois', 5
) v ON v.country_name = c.name
LEFT JOIN states s ON s.country_id = c.id AND s.name = v.name
WHERE s.id IS NULL;

INSERT INTO industries (name, sort_order) VALUES
('Agency or Sales House', 1),
('Agriculture', 2),
('Art and Design', 3),
('Automotive', 4),
('Construction', 5),
('Consulting', 6),
('Education', 7),
('Finance', 8),
('Healthcare', 9),
('Manufacturing', 10),
('Retail', 11),
('Software / IT', 12)
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

INSERT INTO currencies (name, sort_order) VALUES
('INR - Indian Rupee', 1),
('USD - US Dollar', 2),
('EUR - Euro', 3)
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

INSERT INTO languages (name, sort_order) VALUES
('English', 1),
('Hindi', 2),
('Tamil', 3)
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

INSERT INTO timezones (name, sort_order) VALUES
('(GMT 5:30) India Standard Time (Asia/Kolkata)', 1)
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

