CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  email_verified_at TIMESTAMP NULL,
  password VARCHAR(255) NOT NULL,
  mobileno VARCHAR(255) NULL,
  UserType VARCHAR(100),
  branchid INTEGER,
  status INTEGER,
  remember_token VARCHAR(100),
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  companyname VARCHAR(300),
  pincode VARCHAR(8)
);

insert into public.xpermission_branch (userid,branchid,bussid) values(21,3,'8801885553025')


UPDATE public.companyinformation
SET exparieddate = '2025-01-12'
WHERE UserAutoID = 6;

UPDATE companyinformation SET exparieddate = '2025-12-05' WHERE UserAutoID = 21;

select * from companyinformation


CREATE TABLE productgroup (
    id SERIAL PRIMARY KEY,                     -- auto-increment
    productgroupname VARCHAR(100) NOT NULL,
    status INT NOT NULL,
    bussid VARCHAR(100),
    branchid INT,
    enteredby VARCHAR(100),
    updateby VARCHAR(100),
    enteredtime TIMESTAMP,
    updatetime TIMESTAMP,
    CONSTRAINT fk_branch FOREIGN KEY (branchid) REFERENCES branch(id)
);

CREATE TABLE productsubcategory (
    id SERIAL PRIMARY KEY,                               -- auto increment
    productgroupid INT NOT NULL,                         -- FK → productgroup.id
    productsubcategoryname VARCHAR(100) NOT NULL,
    status INT NOT NULL,
    bussid VARCHAR(100),
    branchid INT,                                        -- FK → branch.id
    enteredby VARCHAR(100),
    updateby VARCHAR(100),
    enteredtime TIMESTAMP,
    updatetime TIMESTAMP,

    CONSTRAINT fk_productgroup 
        FOREIGN KEY (productgroupid) REFERENCES productgroup(id),

    CONSTRAINT fk_branch 
        FOREIGN KEY (branchid) REFERENCES branch(id)
);


CREATE TABLE vendo (
    id SERIAL PRIMARY KEY,
    customerid VARCHAR(100) NOT NULL,
    xdate DATE,
    firstname VARCHAR(300),
    lastname VARCHAR(300),
    companyname VARCHAR(300),
    taxid VARCHAR(300),
    contactperson JSONB,                   -- ← FIXED
    vendosince DATE,                       -- ← FIXED
    status VARCHAR(100) NOT NULL,
    balance NUMERIC(18,2),
    balanceas NUMERIC(18,2),
    bal DOUBLE PRECISION NOT NULL,
    street VARCHAR(300),
    city VARCHAR(300),
    zip VARCHAR(300),
    phone VARCHAR(100),
    country VARCHAR(100),
    standardexpensecategory VARCHAR(100),
    standardtaxrate NUMERIC(18,2),
    time TIMESTAMP NOT NULL DEFAULT now(), -- ← FIXED (better than VARCHAR)
    status_st VARCHAR(100) NOT NULL,
    bussid VARCHAR(100) NOT NULL,
    branchid VARCHAR(100) NOT NULL,
    enteredby VARCHAR(100),
    updateby VARCHAR(10)
);

