CREATE DATABASE IF NOT EXISTS billing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE billing;

CREATE TABLE IF NOT EXISTS signup (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    company_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    phone_code VARCHAR(10) NOT NULL DEFAULT '+91',
    password VARCHAR(255) NOT NULL,
    state VARCHAR(120) NOT NULL,
    country VARCHAR(120) NOT NULL,
    org_id VARCHAR(32) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_signup_email (email),
    UNIQUE KEY uq_signup_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    business_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone_country_code VARCHAR(10) NOT NULL DEFAULT '+91',
    phone_number VARCHAR(20) NOT NULL,
    is_phone_verified TINYINT(1) NOT NULL DEFAULT 0,
    workspace_name VARCHAR(100) NOT NULL DEFAULT 'billing',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS signup_otps (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(24) NOT NULL,
    otp_code VARCHAR(6) NOT NULL,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_signup_otps_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organizations (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_name VARCHAR(150) NOT NULL,
    location_country VARCHAR(120) NOT NULL DEFAULT 'India',
    state_name VARCHAR(120) NOT NULL DEFAULT '',
    base_currency VARCHAR(120) NOT NULL DEFAULT 'INR - Indian Rupee',
    fiscal_year VARCHAR(120) NOT NULL DEFAULT 'April - March',
    language_pref VARCHAR(80) NOT NULL DEFAULT 'English',
    timezone_label VARCHAR(180) NOT NULL DEFAULT '(GMT 5:30) India Standard Time (Asia/Kolkata)',
    gst_registered TINYINT(1) NOT NULL DEFAULT 0,
    industry_type VARCHAR(160) NOT NULL DEFAULT '',
    billing_mode VARCHAR(30) NOT NULL DEFAULT 'hybrid',
    billing_source VARCHAR(120) NOT NULL DEFAULT '',
    wants_import TINYINT(1) NOT NULL DEFAULT 1,
    onboarding_completed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_organizations_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Active',
    outstanding_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_customers_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_delete_log (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Deleted',
    outstanding_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    deleted_payload LONGTEXT DEFAULT NULL,
    deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_customer_delete_log_user (user_id),
    KEY idx_customer_delete_log_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deleted_logs (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    table_name VARCHAR(190) NOT NULL,
    record_id BIGINT UNSIGNED NOT NULL,
    deleted_data JSON DEFAULT NULL,
    deleted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_deleted_logs_table_record (table_name, record_id),
    KEY idx_deleted_logs_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    item_name VARCHAR(180) NOT NULL,
    item_type VARCHAR(60) NOT NULL DEFAULT 'Service',
    unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Active',
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    item_description TEXT DEFAULT NULL,
    sales_account VARCHAR(120) NOT NULL DEFAULT 'Sales',
    image_path VARCHAR(255) DEFAULT NULL,
    is_inactive TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_catalog_user (user_id),
    KEY idx_catalog_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_item_history (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    item_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    action_label VARCHAR(80) NOT NULL,
    actor_label VARCHAR(190) NOT NULL DEFAULT '',
    details_text VARCHAR(255) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_catalog_item_history_item (item_id),
    KEY idx_catalog_item_history_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_plans (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    product_item_id INT UNSIGNED NOT NULL,
    plan_name VARCHAR(180) NOT NULL,
    plan_code VARCHAR(80) NOT NULL DEFAULT '',
    plan_description TEXT DEFAULT NULL,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Active',
    billing_frequency_value INT UNSIGNED NOT NULL DEFAULT 1,
    billing_frequency_unit VARCHAR(20) NOT NULL DEFAULT 'Month(s)',
    billing_cycles_mode VARCHAR(60) NOT NULL DEFAULT 'Auto-renews until cancelled',
    billing_cycles_value INT UNSIGNED DEFAULT NULL,
    pricing_model VARCHAR(60) NOT NULL DEFAULT 'Per Unit',
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    setup_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    trial_days INT UNSIGNED NOT NULL DEFAULT 0,
    item_type VARCHAR(20) NOT NULL DEFAULT 'Service',
    plan_account VARCHAR(120) NOT NULL DEFAULT 'Sales',
    setup_fee_account VARCHAR(120) NOT NULL DEFAULT 'Sales',
    pricing_widget_enabled TINYINT(1) NOT NULL DEFAULT 0,
    image_path VARCHAR(255) DEFAULT NULL,
    is_inactive TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_catalog_plans_user (user_id),
    KEY idx_catalog_plans_product (product_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_addons (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    product_item_id INT UNSIGNED NOT NULL,
    addon_name VARCHAR(180) NOT NULL,
    addon_code VARCHAR(80) NOT NULL DEFAULT '',
    addon_description TEXT DEFAULT NULL,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Active',
    addon_type VARCHAR(40) NOT NULL DEFAULT 'One Time',
    pricing_model VARCHAR(60) NOT NULL DEFAULT 'Package',
    billing_frequency_value INT UNSIGNED DEFAULT NULL,
    billing_frequency_unit VARCHAR(20) NOT NULL DEFAULT 'Month(s)',
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    account_name VARCHAR(120) NOT NULL DEFAULT 'Sales',
    image_path VARCHAR(255) DEFAULT NULL,
    is_inactive TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_catalog_addons_user (user_id),
    KEY idx_catalog_addons_product (product_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pricing_widgets (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    widget_name VARCHAR(180) NOT NULL,
    product_item_id INT UNSIGNED NOT NULL,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pricing_widgets_user (user_id),
    KEY idx_pricing_widgets_product (product_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    invoice_number VARCHAR(40) NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    customer_id INT UNSIGNED DEFAULT NULL,
    order_number VARCHAR(80) NOT NULL DEFAULT '',
    salesperson_name VARCHAR(160) NOT NULL DEFAULT '',
    subject VARCHAR(255) NOT NULL DEFAULT '',
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Draft',
    invoice_type VARCHAR(40) NOT NULL DEFAULT 'one_time',
    invoice_date DATE DEFAULT NULL,
    due_date DATE DEFAULT NULL,
    notes_text TEXT DEFAULT NULL,
    terms_text TEXT DEFAULT NULL,
    pdf_template_name VARCHAR(120) NOT NULL DEFAULT 'Spreadsheet Template',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_invoices_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    item_name VARCHAR(180) NOT NULL,
    item_description TEXT DEFAULT NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    account_name VARCHAR(120) NOT NULL DEFAULT 'Sales',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_invoice_items_invoice (invoice_id),
    KEY idx_invoice_items_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quotes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED DEFAULT NULL,
    quote_number VARCHAR(40) NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    reference_number VARCHAR(80) NOT NULL DEFAULT '',
    salesperson_name VARCHAR(160) NOT NULL DEFAULT '',
    project_name VARCHAR(160) NOT NULL DEFAULT '',
    subject VARCHAR(255) NOT NULL DEFAULT '',
    quote_date DATE DEFAULT NULL,
    expiry_date DATE DEFAULT NULL,
    sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Draft',
    quote_type VARCHAR(40) NOT NULL DEFAULT 'Quote',
    customer_notes TEXT DEFAULT NULL,
    terms_text TEXT DEFAULT NULL,
    pdf_template_name VARCHAR(120) NOT NULL DEFAULT 'Spreadsheet Template',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_quotes_number (organization_id, quote_number),
    KEY idx_quotes_user (user_id),
    KEY idx_quotes_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quote_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    quote_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED DEFAULT NULL,
    item_name VARCHAR(180) NOT NULL,
    item_description TEXT DEFAULT NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    account_name VARCHAR(120) NOT NULL DEFAULT 'Sales',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_quote_items_quote (quote_id),
    KEY idx_quote_items_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED DEFAULT NULL,
    subscription_number VARCHAR(40) NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    plan_name VARCHAR(180) NOT NULL DEFAULT '',
    reference_number VARCHAR(80) NOT NULL DEFAULT '',
    salesperson_name VARCHAR(160) NOT NULL DEFAULT '',
    start_date DATE DEFAULT NULL,
    next_billing_date DATE DEFAULT NULL,
    current_cycle_start DATE DEFAULT NULL,
    current_cycle_end DATE DEFAULT NULL,
    billing_frequency_value INT UNSIGNED NOT NULL DEFAULT 1,
    billing_frequency_unit VARCHAR(30) NOT NULL DEFAULT 'week(s)',
    expires_after_cycles INT UNSIGNED DEFAULT NULL,
    renews_forever TINYINT(1) NOT NULL DEFAULT 1,
    payment_terms VARCHAR(120) NOT NULL DEFAULT 'Due on Receipt',
    payment_mode VARCHAR(40) NOT NULL DEFAULT 'offline',
    autocharge_enabled TINYINT(1) NOT NULL DEFAULT 0,
    prorate_enabled TINYINT(1) NOT NULL DEFAULT 1,
    generate_invoice_on_prorate TINYINT(1) NOT NULL DEFAULT 1,
    sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Draft',
    customer_notes TEXT DEFAULT NULL,
    terms_text TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_subscriptions_number (organization_id, subscription_number),
    KEY idx_subscriptions_user (user_id),
    KEY idx_subscriptions_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    subscription_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED DEFAULT NULL,
    item_name VARCHAR(180) NOT NULL,
    item_description TEXT DEFAULT NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    item_role VARCHAR(30) NOT NULL DEFAULT 'plan',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_subscription_items_subscription (subscription_id),
    KEY idx_subscription_items_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_challans (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED DEFAULT NULL,
    delivery_challan_number VARCHAR(40) NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    reference_number VARCHAR(80) NOT NULL DEFAULT '',
    challan_date DATE DEFAULT NULL,
    challan_type VARCHAR(80) NOT NULL DEFAULT '',
    sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Draft',
    customer_notes TEXT DEFAULT NULL,
    terms_text TEXT DEFAULT NULL,
    pdf_template_name VARCHAR(120) NOT NULL DEFAULT 'Standard Template',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_delivery_challans_number (organization_id, delivery_challan_number),
    KEY idx_delivery_challans_user (user_id),
    KEY idx_delivery_challans_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_challan_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    delivery_challan_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED DEFAULT NULL,
    item_name VARCHAR(180) NOT NULL,
    item_description TEXT DEFAULT NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_delivery_challan_items_doc (delivery_challan_id),
    KEY idx_delivery_challan_items_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_notes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED DEFAULT NULL,
    invoice_id INT UNSIGNED DEFAULT NULL,
    credit_note_number VARCHAR(40) NOT NULL,
    customer_name VARCHAR(160) NOT NULL,
    reference_number VARCHAR(80) NOT NULL DEFAULT '',
    salesperson_name VARCHAR(160) NOT NULL DEFAULT '',
    subject VARCHAR(255) NOT NULL DEFAULT '',
    credit_note_date DATE DEFAULT NULL,
    sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    credits_remaining DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_label VARCHAR(50) NOT NULL DEFAULT 'Draft',
    customer_notes TEXT DEFAULT NULL,
    terms_text TEXT DEFAULT NULL,
    pdf_template_name VARCHAR(120) NOT NULL DEFAULT 'Spreadsheet Template',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_credit_notes_number (organization_id, credit_note_number),
    KEY idx_credit_notes_user (user_id),
    KEY idx_credit_notes_customer (customer_id),
    KEY idx_credit_notes_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_note_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    credit_note_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED DEFAULT NULL,
    item_name VARCHAR(180) NOT NULL,
    item_description TEXT DEFAULT NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
    unit_name VARCHAR(60) NOT NULL DEFAULT '',
    rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    account_name VARCHAR(120) NOT NULL DEFAULT 'Sales',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_credit_note_items_note (credit_note_id),
    KEY idx_credit_note_items_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
