-- SQL script to create tables for VAT, sales tax, and country information

-- Create table for VAT rates and rules
CREATE TABLE vat_rules (
    country_code TEXT PRIMARY KEY,
    vat_rate REAL DEFAULT NULL,
    reverse_charge BOOLEAN DEFAULT NULL
);

-- Create table for customer rules related to VAT
CREATE TABLE customer_rules (
    country_code TEXT,
    customer_vat_registered BOOLEAN DEFAULT NULL,
    vat_rate REAL DEFAULT NULL,
    reverse_charge BOOLEAN DEFAULT NULL,
    FOREIGN KEY (country_code) REFERENCES vat_rules(country_code)
);

-- Create table for vendor rules related to VAT
CREATE TABLE vendor_rules (
    country_code TEXT,
    vendor_vat_registered BOOLEAN DEFAULT NULL,
    vat_rate REAL DEFAULT NULL,
    reverse_charge BOOLEAN DEFAULT NULL,
    FOREIGN KEY (country_code) REFERENCES vat_rules(country_code)
);

-- Create table for U.S. sales tax compliance
CREATE TABLE us_sales_tax_compliance (
    state_code TEXT PRIMARY KEY,   -- U.S. state code (e.g., CA, NY)
    sales_threshold REAL,          -- Sales threshold in USD (e.g., 100000)
    transaction_threshold INTEGER, -- Number of transactions threshold (e.g., 200)
    sales_tax_rate REAL,           -- State sales tax rate (e.g., 7.25)
    threshold_reached BOOLEAN,     -- True/False if threshold is reached
    sales_block BOOLEAN,           -- True/False if sales to this state are blocked
    total_sales REAL DEFAULT 0,    -- Total sales in the state
    total_transactions INTEGER DEFAULT 0  -- Total number of transactions in the state
);

-- Create table for country and currency details
CREATE TABLE countries (
    country_code TEXT PRIMARY KEY,
    country_name TEXT,
    continent TEXT,
    currency TEXT
);