CREATE TABLE IF NOT EXISTS tenants(id INTEGER PRIMARY KEY,name TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS contacts(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL REFERENCES tenants(id),name TEXT NOT NULL,code TEXT,email TEXT,phone TEXT,role TEXT NOT NULL,balance INTEGER NOT NULL DEFAULT 0,UNIQUE(tenant,id));
CREATE TABLE IF NOT EXISTS products(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL REFERENCES tenants(id),name TEXT NOT NULL,code TEXT NOT NULL,type TEXT NOT NULL,unit TEXT NOT NULL,price INTEGER NOT NULL,tax INTEGER NOT NULL,stock INTEGER NOT NULL DEFAULT 0,minimum INTEGER NOT NULL DEFAULT 0,category TEXT DEFAULT 'Genel',UNIQUE(tenant,code),UNIQUE(tenant,id));
CREATE TABLE IF NOT EXISTS invoices(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL REFERENCES tenants(id),number TEXT NOT NULL,contact_id INTEGER NOT NULL,product_id INTEGER NOT NULL,quantity INTEGER NOT NULL,unit_price INTEGER NOT NULL,discount INTEGER NOT NULL,net INTEGER NOT NULL,total INTEGER NOT NULL,kind TEXT NOT NULL,date TEXT NOT NULL,status TEXT NOT NULL DEFAULT 'draft',UNIQUE(tenant,number),FOREIGN KEY(tenant,contact_id) REFERENCES contacts(tenant,id),FOREIGN KEY(tenant,product_id) REFERENCES products(tenant,id));
CREATE TABLE IF NOT EXISTS payments(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL REFERENCES tenants(id),contact_id INTEGER NOT NULL,amount INTEGER NOT NULL,kind TEXT NOT NULL,date TEXT NOT NULL,FOREIGN KEY(tenant,contact_id) REFERENCES contacts(tenant,id));
CREATE TABLE IF NOT EXISTS ledger(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL,invoice_id INTEGER,product_id INTEGER,contact_id INTEGER,stock_delta INTEGER NOT NULL,amount INTEGER NOT NULL,event TEXT NOT NULL,created_at TEXT DEFAULT CURRENT_TIMESTAMP,UNIQUE(tenant,invoice_id,event));
CREATE TABLE IF NOT EXISTS requests(tenant INTEGER NOT NULL,key TEXT NOT NULL,response TEXT NOT NULL,PRIMARY KEY(tenant,key));
CREATE TABLE IF NOT EXISTS pos_sales(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL,number TEXT NOT NULL,contact_id INTEGER NOT NULL,total INTEGER NOT NULL,cash INTEGER NOT NULL,card_pending INTEGER NOT NULL,credit INTEGER NOT NULL,date TEXT NOT NULL,UNIQUE(tenant,number),FOREIGN KEY(tenant,contact_id) REFERENCES contacts(tenant,id));
CREATE TABLE IF NOT EXISTS pos_lines(id INTEGER PRIMARY KEY,sale_id INTEGER NOT NULL REFERENCES pos_sales(id),invoice_id INTEGER NOT NULL REFERENCES invoices(id));
ALTER TABLE contacts ADD COLUMN entity_type TEXT NOT NULL DEFAULT 'company';
ALTER TABLE contacts ADD COLUMN tax_number TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN tax_office TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN address TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN city TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN district TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN contact_person TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN notes TEXT DEFAULT '';
ALTER TABLE contacts ADD COLUMN due_days INTEGER NOT NULL DEFAULT 0;
ALTER TABLE contacts ADD COLUMN risk_limit INTEGER NOT NULL DEFAULT 0;
ALTER TABLE contacts ADD COLUMN version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE products ADD COLUMN barcode TEXT DEFAULT '';
ALTER TABLE products ADD COLUMN brand TEXT DEFAULT '';
ALTER TABLE products ADD COLUMN purchase_price INTEGER NOT NULL DEFAULT 0;
ALTER TABLE products ADD COLUMN notes TEXT DEFAULT '';
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 1;
CREATE UNIQUE INDEX IF NOT EXISTS product_barcode_tenant ON products(tenant,barcode) WHERE barcode<>'';CREATE UNIQUE INDEX IF NOT EXISTS contact_code_tenant ON contacts(tenant,code);CREATE TABLE IF NOT EXISTS stock_adjustments(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL,product_id INTEGER NOT NULL,delta INTEGER NOT NULL,reason TEXT NOT NULL,created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(tenant,product_id) REFERENCES products(tenant,id));CREATE TABLE IF NOT EXISTS pos_returns(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL,sale_id INTEGER NOT NULL UNIQUE REFERENCES pos_sales(id),reason TEXT NOT NULL,total INTEGER NOT NULL,cash_refund INTEGER NOT NULL,card_reversal INTEGER NOT NULL,credit_reversal INTEGER NOT NULL,created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS pos_sales_tenant_date ON pos_sales(tenant,date,id);CREATE INDEX IF NOT EXISTS invoice_tenant_date ON invoices(tenant,date,status);CREATE INDEX IF NOT EXISTS ledger_tenant_contact ON ledger(tenant,contact_id);CREATE TABLE IF NOT EXISTS pos_line_snapshots(invoice_id INTEGER PRIMARY KEY REFERENCES invoices(id),tenant INTEGER NOT NULL REFERENCES tenants(id),name TEXT NOT NULL,code TEXT NOT NULL,unit TEXT NOT NULL,tax INTEGER NOT NULL);ALTER TABLE requests ADD COLUMN fingerprint TEXT;
CREATE TABLE memberships(user_id INTEGER NOT NULL REFERENCES users(id),tenant INTEGER NOT NULL REFERENCES tenants(id),role TEXT NOT NULL CHECK(role IN ('owner','manager','cashier','viewer')),PRIMARY KEY(user_id,tenant));
CREATE TABLE audit_events(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL REFERENCES tenants(id),user_id INTEGER REFERENCES users(id),action TEXT NOT NULL,target TEXT,created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE partial_returns(id INTEGER PRIMARY KEY,tenant INTEGER NOT NULL REFERENCES tenants(id),sale_id INTEGER NOT NULL REFERENCES pos_sales(id),reason TEXT NOT NULL,total INTEGER NOT NULL,cash_refund INTEGER NOT NULL,card_reversal INTEGER NOT NULL,credit_reversal INTEGER NOT NULL,created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE partial_return_lines(id INTEGER PRIMARY KEY,return_id INTEGER NOT NULL REFERENCES partial_returns(id),invoice_id INTEGER NOT NULL REFERENCES invoices(id),quantity INTEGER NOT NULL,total INTEGER NOT NULL,net INTEGER NOT NULL);
CREATE INDEX partial_sale ON partial_returns(tenant,sale_id);
CREATE INDEX audit_tenant ON audit_events(tenant,id);
CREATE INDEX invoice_tenant_contact ON invoices(tenant,contact_id);