-- local admins table
CREATE TABLE IF NOT EXISTS local_admins (
    local_admin_pubkey TEXT PRIMARY KEY,
    admin_pubkey TEXT NOT NULL,
    state_name TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    companies_managed INTEGER NOT NULL DEFAULT 0,
    registry_pubkey TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- companies table 
CREATE TABLE IF NOT EXISTS companies (
    company_pubkey TEXT PRIMARY KEY,
    company_name TEXT NOT NULL,
    company_admin_pubkey TEXT NOT NULL,
    local_admin_pubkey TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    eua_vault TEXT NOT NULL,
    ghg_vault TEXT NOT NULL,
    usdc_vault TEXT NOT NULL,
    locked_eua_amount BIGINT NOT NULL DEFAULT 0,
    locked_usdc_amount BIGINT NOT NULL DEFAULT 0,
    in_auction BOOLEAN NOT NULL DEFAULT false,
    placed_bid BOOLEAN NOT NULL DEFAULT false,
    auction_id BIGINT,
    usdc_balance BIGINT NOT NULL DEFAULT 0,
    eua_balance BIGINT NOT NULL DEFAULT 0,
    ghg_balance BIGINT NOT NULL DEFAULT 0,
    registry_pubkey TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add foreign key constraint for companies referencing local_admins
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'companies_local_admin_pubkey_fkey'
    ) THEN
        ALTER TABLE companies 
        ADD CONSTRAINT companies_local_admin_pubkey_fkey 
        FOREIGN KEY (local_admin_pubkey) 
        REFERENCES local_admins(local_admin_pubkey);
    END IF;
END $$;

-- EUA Minting Log
CREATE TABLE IF NOT EXISTS eua_minting_log (
    id SERIAL PRIMARY KEY,
    company_pubkey TEXT NOT NULL,
    company_name TEXT NOT NULL,
    eua_vault TEXT NOT NULL,
    amount BIGINT NOT NULL,
    minted_by TEXT NOT NULL,
    eua_mint TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add foreign key constraint for eua_minting_log
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'eua_minting_log_company_pubkey_fkey'
    ) THEN
        ALTER TABLE eua_minting_log 
        ADD CONSTRAINT eua_minting_log_company_pubkey_fkey 
        FOREIGN KEY (company_pubkey) 
        REFERENCES companies(company_pubkey);
    END IF;
END $$;

-- GHG Minting Log
CREATE TABLE IF NOT EXISTS ghg_minting_log (
    id SERIAL PRIMARY KEY,
    company_pubkey TEXT NOT NULL,
    company_name TEXT NOT NULL,
    ghg_vault TEXT NOT NULL,
    amount BIGINT NOT NULL,
    local_admin_pubkey TEXT NOT NULL,
    local_admin_state TEXT NOT NULL,
    verification_note TEXT NOT NULL,
    ghg_mint TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add foreign key constraint for ghg_minting_log
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'ghg_minting_log_company_pubkey_fkey'
    ) THEN
        ALTER TABLE ghg_minting_log 
        ADD CONSTRAINT ghg_minting_log_company_pubkey_fkey 
        FOREIGN KEY (company_pubkey) 
        REFERENCES companies(company_pubkey);
    END IF;
END $$;

-- EUA Transfer Log
CREATE TABLE IF NOT EXISTS eua_transfer_log (
    id SERIAL PRIMARY KEY,
    from_company_pubkey TEXT NOT NULL,
    from_company_name TEXT NOT NULL,
    to_company_pubkey TEXT NOT NULL,
    to_company_name TEXT NOT NULL,
    from_vault TEXT NOT NULL,
    to_vault TEXT NOT NULL,
    amount BIGINT NOT NULL,
    authorized_by TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add foreign key constraints for eua_transfer_log (TWO foreign keys to companies table)
DO $$ 
BEGIN
    -- FK for from_company_pubkey
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'eua_transfer_log_from_company_pubkey_fkey'
    ) THEN
        ALTER TABLE eua_transfer_log 
        ADD CONSTRAINT eua_transfer_log_from_company_pubkey_fkey 
        FOREIGN KEY (from_company_pubkey) 
        REFERENCES companies(company_pubkey);
    END IF;
    
    -- FK for to_company_pubkey
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'eua_transfer_log_to_company_pubkey_fkey'
    ) THEN
        ALTER TABLE eua_transfer_log 
        ADD CONSTRAINT eua_transfer_log_to_company_pubkey_fkey 
        FOREIGN KEY (to_company_pubkey) 
        REFERENCES companies(company_pubkey);
    END IF;
END $$;

-- Indexes for better query performance (created after tables exist)
CREATE INDEX IF NOT EXISTS idx_companies_local_admin ON companies(local_admin_pubkey);
CREATE INDEX IF NOT EXISTS idx_companies_active ON companies(is_active);
CREATE INDEX IF NOT EXISTS idx_local_admins_active ON local_admins(is_active);
CREATE INDEX IF NOT EXISTS idx_eua_minting_company ON eua_minting_log(company_pubkey);
CREATE INDEX IF NOT EXISTS idx_ghg_minting_company ON ghg_minting_log(company_pubkey);
CREATE INDEX IF NOT EXISTS idx_eua_transfer_from ON eua_transfer_log(from_company_pubkey);
CREATE INDEX IF NOT EXISTS idx_eua_transfer_to ON eua_transfer_log(to_company_pubkey);
CREATE INDEX IF NOT EXISTS idx_companies_in_auction ON companies(in_auction);
CREATE INDEX IF NOT EXISTS idx_companies_auction_id ON companies(auction_id) WHERE auction_id IS NOT NULL;