-- Invoice Management System
-- Phase 1: Core invoice CRUD with multi-currency and flexible tax support

-- ============================================================================
-- INVOICES TABLE
-- ============================================================================
create table "public"."invoices" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone not null default now(),
    
    -- Invoice identification
    "invoice_number" text not null,
    "reference" text, -- Optional external reference
    
    -- Relationships (all optional to support flexible linking)
    "company_id" bigint,
    "contact_id" bigint,
    "deal_id" bigint,
    "sales_id" bigint,
    
    -- Invoice details
    "status" text not null default 'draft', -- draft, sent, paid, overdue, cancelled
    "issue_date" timestamp with time zone not null default now(),
    "due_date" timestamp with time zone not null,
    "paid_at" timestamp with time zone,
    
    -- Multi-currency support
    "currency" text not null default 'USD', -- ISO 4217 currency code
    
    -- Financial totals (stored in invoice currency)
    "subtotal" numeric(15,2) not null default 0,
    "tax_total" numeric(15,2) not null default 0,
    "total" numeric(15,2) not null default 0,
    "amount_paid" numeric(15,2) not null default 0, -- For partial payments
    
    -- Additional information
    "notes" text,
    "payment_terms" text,
    "terms_and_conditions" text,
    
    -- Metadata
    "sent_at" timestamp with time zone, -- When invoice was sent
    "viewed_at" timestamp with time zone, -- When invoice was first viewed by client
    
    constraint "invoices_pkey" primary key ("id")
);

-- ============================================================================
-- INVOICE ITEMS TABLE
-- ============================================================================
create table "public"."invoice_items" (
    "id" bigint generated by default as identity not null,
    "invoice_id" bigint not null,
    
    -- Item details
    "description" text not null,
    "quantity" numeric(10,2) not null default 1,
    "unit_price" numeric(15,2) not null,
    
    -- Tax configuration (flexible per-item)
    "tax_rate" numeric(5,2) not null default 0, -- Percentage (e.g., 20.00 for 20%)
    "tax_name" text, -- e.g., "VAT", "GST", "Sales Tax", "消費税" (Japan)
    "tax_amount" numeric(15,2) not null default 0, -- Calculated tax amount
    
    -- Item categorization
    "item_type" text default 'service', -- service, product, hour, day, deposit
    
    -- Calculated totals
    "line_total" numeric(15,2) not null default 0, -- quantity * unit_price
    "line_total_with_tax" numeric(15,2) not null default 0, -- line_total + tax_amount
    
    -- Ordering
    "sort_order" smallint not null default 0,
    
    constraint "invoice_items_pkey" primary key ("id"),
    constraint "invoice_items_invoice_id_fkey" foreign key ("invoice_id") 
        references "invoices"("id") on delete cascade
);

-- ============================================================================
-- INVOICE NOTES TABLE (Activity tracking)
-- ============================================================================
create table "public"."invoice_notes" (
    "id" bigint generated by default as identity not null,
    "invoice_id" bigint not null,
    "text" text,
    "date" timestamp with time zone default now(),
    "sales_id" bigint,
    "attachments" jsonb[],
    
    constraint "invoice_notes_pkey" primary key ("id"),
    constraint "invoice_notes_invoice_id_fkey" foreign key ("invoice_id") 
        references "invoices"("id") on delete cascade,
    constraint "invoice_notes_sales_id_fkey" foreign key ("sales_id") 
        references "sales"("id") on delete set null
);

-- ============================================================================
-- TAX PRESETS TABLE (For common tax configurations)
-- ============================================================================
create table "public"."tax_presets" (
    "id" bigint generated by default as identity not null,
    "name" text not null, -- e.g., "US Sales Tax - CA", "EU VAT - Standard", "Japan Consumption Tax"
    "region" text not null, -- e.g., "US", "CA", "EU", "JP", "KR"
    "tax_rate" numeric(5,2) not null, -- Percentage
    "description" text,
    "is_active" boolean not null default true,
    "created_at" timestamp with time zone not null default now(),
    
    constraint "tax_presets_pkey" primary key ("id")
);

-- ============================================================================
-- FOREIGN KEY CONSTRAINTS
-- ============================================================================
alter table "invoices" add constraint "invoices_company_id_fkey" 
    foreign key ("company_id") references "companies"("id") on delete set null;

alter table "invoices" add constraint "invoices_contact_id_fkey" 
    foreign key ("contact_id") references "contacts"("id") on delete set null;

alter table "invoices" add constraint "invoices_deal_id_fkey" 
    foreign key ("deal_id") references "deals"("id") on delete set null;

alter table "invoices" add constraint "invoices_sales_id_fkey" 
    foreign key ("sales_id") references "sales"("id") on delete set null;

-- ============================================================================
-- INDEXES
-- ============================================================================
create index "invoices_company_id_idx" on "invoices"("company_id");
create index "invoices_contact_id_idx" on "invoices"("contact_id");
create index "invoices_deal_id_idx" on "invoices"("deal_id");
create index "invoices_sales_id_idx" on "invoices"("sales_id");
create index "invoices_status_idx" on "invoices"("status");
create index "invoices_issue_date_idx" on "invoices"("issue_date");
create index "invoices_due_date_idx" on "invoices"("due_date");
create unique index "invoices_invoice_number_idx" on "invoices"("invoice_number");

create index "invoice_items_invoice_id_idx" on "invoice_items"("invoice_id");
create index "invoice_notes_invoice_id_idx" on "invoice_notes"("invoice_id");

-- ============================================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================================
alter table "invoices" enable row level security;
alter table "invoice_items" enable row level security;
alter table "invoice_notes" enable row level security;
alter table "tax_presets" enable row level security;

-- Invoices policies
create policy "Enable read access for authenticated users" on "invoices"
    for select to authenticated using (true);

create policy "Enable insert for authenticated users" on "invoices"
    for insert to authenticated with check (true);

create policy "Enable update for authenticated users" on "invoices"
    for update to authenticated using (true) with check (true);

create policy "Enable delete for authenticated users" on "invoices"
    for delete to authenticated using (true);

-- Invoice items policies
create policy "Enable read access for authenticated users" on "invoice_items"
    for select to authenticated using (true);

create policy "Enable insert for authenticated users" on "invoice_items"
    for insert to authenticated with check (true);

create policy "Enable update for authenticated users" on "invoice_items"
    for update to authenticated using (true) with check (true);

create policy "Enable delete for authenticated users" on "invoice_items"
    for delete to authenticated using (true);

-- Invoice notes policies
create policy "Enable read access for authenticated users" on "invoice_notes"
    for select to authenticated using (true);

create policy "Enable insert for authenticated users" on "invoice_notes"
    for insert to authenticated with check (true);

create policy "Enable update for authenticated users" on "invoice_notes"
    for update to authenticated using (true) with check (true);

create policy "Enable delete for authenticated users" on "invoice_notes"
    for delete to authenticated using (true);

-- Tax presets policies
create policy "Enable read access for authenticated users" on "tax_presets"
    for select to authenticated using (true);

create policy "Enable insert for authenticated users" on "tax_presets"
    for insert to authenticated with check (true);

create policy "Enable update for authenticated users" on "tax_presets"
    for update to authenticated using (true) with check (true);

create policy "Enable delete for authenticated users" on "tax_presets"
    for delete to authenticated using (true);

-- ============================================================================
-- GRANTS
-- ============================================================================
grant all on "invoices" to authenticated;
grant all on "invoice_items" to authenticated;
grant all on "invoice_notes" to authenticated;
grant all on "tax_presets" to authenticated;

grant all on "invoices" to service_role;
grant all on "invoice_items" to service_role;
grant all on "invoice_notes" to service_role;
grant all on "tax_presets" to service_role;

-- ============================================================================
-- TRIGGERS FOR UPDATED_AT
-- ============================================================================
create or replace function update_updated_at_column()
returns trigger as $$
begin
    new.updated_at = now();
    return new;
end;
$$ language plpgsql;

create trigger update_invoices_updated_at before update on invoices
    for each row execute function update_updated_at_column();

-- ============================================================================
-- SEED DEFAULT TAX PRESETS
-- ============================================================================
insert into "tax_presets" ("name", "region", "tax_rate", "description") values
    -- United States
    ('US Sales Tax - California', 'US', 7.25, 'California state sales tax'),
    ('US Sales Tax - New York', 'US', 4.00, 'New York state sales tax'),
    ('US Sales Tax - Texas', 'US', 6.25, 'Texas state sales tax'),
    ('US Sales Tax - Florida', 'US', 6.00, 'Florida state sales tax'),
    
    -- Canada
    ('Canada GST', 'CA', 5.00, 'Goods and Services Tax'),
    ('Canada HST - Ontario', 'CA', 13.00, 'Harmonized Sales Tax - Ontario'),
    ('Canada HST - Nova Scotia', 'CA', 15.00, 'Harmonized Sales Tax - Nova Scotia'),
    ('Canada QST - Quebec', 'CA', 9.975, 'Quebec Sales Tax'),
    
    -- Europe
    ('EU VAT - Standard (20%)', 'EU', 20.00, 'Standard VAT rate for most EU countries'),
    ('EU VAT - Reduced (10%)', 'EU', 10.00, 'Reduced VAT rate'),
    ('EU VAT - Germany', 'EU', 19.00, 'German VAT (Mehrwertsteuer)'),
    ('EU VAT - France', 'EU', 20.00, 'French VAT (TVA)'),
    ('EU VAT - Spain', 'EU', 21.00, 'Spanish VAT (IVA)'),
    ('EU VAT - Italy', 'EU', 22.00, 'Italian VAT (IVA)'),
    
    -- Japan
    ('Japan Consumption Tax', 'JP', 10.00, '消費税 (Shōhizei)'),
    ('Japan Reduced Rate', 'JP', 8.00, 'Reduced consumption tax rate'),
    
    -- Korea
    ('Korea VAT', 'KR', 10.00, '부가가치세 (Value Added Tax)'),
    
    -- Zero-rated / Exempt
    ('Tax Exempt', 'GLOBAL', 0.00, 'No tax applied');
