Changeset 06ebe74 for server.js


Ignore:
Timestamp:
09/20/26 06:25:04 (10 days ago)
Author:
Klimentina Efremova <klimentina08642@…>
Branches:
finki-main, main
Children:
6149556
Parents:
33517cc
Message:

Created successfull connection to university database through SSH tunnel

File:
1 edited

Legend:

Unmodified
Added
Removed

  • server.js

    r33517cc r06ebe74  
    2525    const emailConfig = {
    2626        host: process.env.SMTP_HOST || 'smtp.gmail.com',
    27         port: parseInt(process.env.SMTP_PORT) || 587,
     27        port: (() => {
     28            const configuredPort = parseInt(process.env.SMTP_PORT, 10);
     29            if (process.env.SMTP_HOST === 'smtp.ethereal.email' && configuredPort === 3000) return 587;
     30            return configuredPort || 587;
     31        })(),
    2832        secure: false,
    2933        auth: {
    … …  
    169173    let code = '';
    170174    for(let i = 0; i < 6; i++) {
    171         code += crypto.randomInt(0, 9);
     175        code += crypto.randomInt(0, 10);
    172176    }
    173177    return code;
    … …  
    386390                params = [];
    387391            }
    388             const normalized = String(sql).trim().toUpperCase();
     392            const normalized = String(sql).trim().replace(/;\s*$/, '').toUpperCase();
     393
    389394            if (normalized === 'BEGIN TRANSACTION' || normalized === 'BEGIN') {
    390395                if (transactionClient) {
    … …  
    403408                return;
    404409            }
     410
    405411            if (normalized === 'COMMIT') {
    406412                if (!transactionClient) {
    … …  
    422428                return;
    423429            }
     430
    424431            if (normalized === 'ROLLBACK') {
    425432                if (!transactionClient) {
    … …  
    441448                return;
    442449            }
     450
    443451            dbQuery(sql, params || [], (err, result) => {
    444452                if (callback) {
    … …  
    453461
    454462    async initializeDatabase() {
     463        // The database supplied by the project is authoritative.  Existing tables
     464        // are removed before recreation so an old incompatible schema can never
     465        // survive a restart and cause CREATE TABLE IF NOT EXISTS to skip columns.
     466        const schemaCompatibility = await pool.query(`
     467            SELECT
     468                EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='product') AS product_exists,
     469                EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='category_id') AS product_has_category,
     470                EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='store_id') AS product_has_store,
     471                EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='order' AND column_name='store_id') AS order_has_store,
     472                EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='request' AND column_name='store_id') AS request_has_store,
     473                EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='review' AND column_name='review_id') AS review_has_review_id,
     474                EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='refund' AND column_name='request_date') AS refund_has_request_date
     475        `);
     476
     477        const c = schemaCompatibility.rows[0];
     478        const forceReset = String(process.env.RESET_DATABASE || '').toLowerCase() === 'true' || process.env.RESET_DATABASE === '1';
     479        const schemaMismatch = !c.product_exists || !c.product_has_category || c.product_has_store || c.order_has_store || c.request_has_store || c.review_has_review_id || c.refund_has_request_date;
     480        const resetDatabase = forceReset || schemaMismatch;
     481
     482        if (resetDatabase) {
     483            console.log('🧹 Existing PostgreSQL schema is incompatible with the project schema. Recreating project tables...');
     484            await pool.query(`
     485                DROP TABLE IF EXISTS audit_log CASCADE;
     486                DROP TABLE IF EXISTS user_roles CASCADE;
     487                DROP TABLE IF EXISTS roles CASCADE;
     488                DROP TABLE IF EXISTS users CASCADE;
     489                DROP TABLE IF EXISTS approves CASCADE;
     490                DROP TABLE IF EXISTS includes CASCADE;
     491                DROP TABLE IF EXISTS sells CASCADE;
     492                DROP TABLE IF EXISTS worked CASCADE;
     493                DROP TABLE IF EXISTS works_in_store CASCADE;
     494                DROP TABLE IF EXISTS makes_change CASCADE;
     495                DROP TABLE IF EXISTS "change" CASCADE;
     496                DROP TABLE IF EXISTS for_store CASCADE;
     497                DROP TABLE IF EXISTS answers CASCADE;
     498                DROP TABLE IF EXISTS makes_request CASCADE;
     499                DROP TABLE IF EXISTS request CASCADE;
     500                DROP TABLE IF EXISTS exchanges_data CASCADE;
     501                DROP TABLE IF EXISTS monthly_profit CASCADE;
     502                DROP TABLE IF EXISTS report CASCADE;
     503                DROP TABLE IF EXISTS refund CASCADE;
     504                DROP TABLE IF EXISTS review CASCADE;
     505                DROP TABLE IF EXISTS "order" CASCADE;
     506                DROP TABLE IF EXISTS delivery_address CASCADE;
     507                DROP TABLE IF EXISTS client CASCADE;
     508                DROP TABLE IF EXISTS employees CASCADE;
     509                DROP TABLE IF EXISTS boss CASCADE;
     510                DROP TABLE IF EXISTS permissions CASCADE;
     511                DROP TABLE IF EXISTS personal CASCADE;
     512                DROP TABLE IF EXISTS color CASCADE;
     513                DROP TABLE IF EXISTS image CASCADE;
     514                DROP TABLE IF EXISTS product CASCADE;
     515                DROP TABLE IF EXISTS store CASCADE;
     516                DROP TABLE IF EXISTS category CASCADE;
     517            `);
     518        }
     519
    455520        const schema = `
    456 
    457521            CREATE TABLE IF NOT EXISTS category (
    458                                                     id SERIAL PRIMARY KEY,
    459                                                     name VARCHAR(50) NOT NULL,
    460                 parent_category_id INTEGER REFERENCES category(id) ON DELETE SET NULL
    461                 );
     522                id SERIAL PRIMARY KEY,
     523                name VARCHAR(50) NOT NULL,
     524                parent_category_id INTEGER REFERENCES category(id) NOT NULL
     525            );
     526
     527            CREATE TABLE IF NOT EXISTS product (
     528                code VARCHAR(8) PRIMARY KEY DEFAULT '-1',
     529                price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
     530                availability INTEGER NOT NULL,
     531                weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),
     532                width_x_length_x_depth VARCHAR(20) NOT NULL,
     533                aprox_production_time INTEGER NOT NULL,
     534                description VARCHAR(500) NOT NULL,
     535                category_id INTEGER NOT NULL REFERENCES category(id) ON DELETE SET DEFAULT
     536            );
     537
     538            CREATE TABLE IF NOT EXISTS image (
     539                product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
     540                image VARCHAR NOT NULL DEFAULT 'Image NOT found!'
     541            );
     542
     543            CREATE TABLE IF NOT EXISTS color (
     544                product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
     545                color VARCHAR(50)
     546            );
     547
    462548            CREATE TABLE IF NOT EXISTS store (
    463                                                  store_id VARCHAR(3) PRIMARY KEY,
     549                store_ID VARCHAR(3) PRIMARY KEY,
    464550                name VARCHAR(50) UNIQUE NOT NULL,
    465551                date_of_founding DATE NOT NULL,
    466552                physical_address VARCHAR(100) NOT NULL,
    467553                store_email VARCHAR(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    468                 rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating >= 0 AND rating <= 5)
    469                 );
     554                rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0)
     555            );
     556
    470557            CREATE TABLE IF NOT EXISTS personal (
    471                                                     id VARCHAR(10) PRIMARY KEY,
     558                id VARCHAR(10) PRIMARY KEY,
    472559                first_name VARCHAR(20) NOT NULL,
    473560                last_name VARCHAR(20) NOT NULL,
    474                 ssn VARCHAR(13) UNIQUE NOT NULL CHECK (ssn ~ '^[0-9]{13}$'),
     561                ssn VARCHAR(13) NOT NULL CHECK (ssn ~ '^[0-9]{13}$'),
    475562                email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    476563                password VARCHAR NOT NULL
    477                 );
     564            );
     565
    478566            CREATE TABLE IF NOT EXISTS permissions (
    479                                                        personal_is VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
     567                personal_is VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
    480568                type VARCHAR(50) NOT NULL,
    481569                authorisation VARCHAR(50) NOT NULL
    482                 );
     570            );
     571
    483572            CREATE TABLE IF NOT EXISTS boss (
    484                                                 boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE
    485                 );
     573                boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE
     574            );
     575
    486576            CREATE TABLE IF NOT EXISTS employees (
    487                                                      employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
     577                employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
    488578                date_of_hire DATE NOT NULL
    489                 );
     579            );
     580
    490581            CREATE TABLE IF NOT EXISTS client (
    491                                                   client_id SERIAL PRIMARY KEY,
    492                                                   first_name VARCHAR(50) NOT NULL,
     582                client_ID SERIAL PRIMARY KEY,
     583                first_name VARCHAR(50) NOT NULL,
    493584                last_name VARCHAR(50) NOT NULL,
    494585                email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    495586                password VARCHAR NOT NULL
    496                 );
    497             CREATE TABLE IF NOT EXISTS product (
    498                                                    code VARCHAR(8) PRIMARY KEY,
    499                 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    500                 availability INTEGER NOT NULL DEFAULT 0,
    501                 weight DECIMAL(5,2) NOT NULL CHECK (weight > 0),
    502                 width_x_length_x_depth VARCHAR(20) NOT NULL,
    503                 aprox_production_time INTEGER NOT NULL,
    504                 description VARCHAR(500) NOT NULL,
    505                 category_id INTEGER NOT NULL REFERENCES category(id) ON DELETE SET NULL,
    506                 store_id VARCHAR(3) REFERENCES store(store_id) ON DELETE CASCADE
    507                 );
    508             CREATE TABLE IF NOT EXISTS image (
    509                                                  product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
    510                 image VARCHAR NOT NULL DEFAULT 'Image not found!'
    511                 );
    512             CREATE TABLE IF NOT EXISTS color (
    513                                                  product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
    514                 color VARCHAR(50)
    515                 );
     587            );
     588
    516589            CREATE TABLE IF NOT EXISTS delivery_address (
    517                                                             client_id INTEGER PRIMARY KEY REFERENCES client(client_id) ON DELETE CASCADE,
     590                client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID) ON DELETE CASCADE,
    518591                address VARCHAR(200) NOT NULL,
    519592                city VARCHAR(30) NOT NULL,
    … …  
    521594                country VARCHAR(40) NOT NULL,
    522595                is_default BOOLEAN DEFAULT TRUE
    523                 );
     596            );
     597
    524598            CREATE TABLE IF NOT EXISTS "order" (
    525                                                    order_num VARCHAR(11) PRIMARY KEY,
    526                 client_id INTEGER REFERENCES client(client_id) ON DELETE CASCADE,
     599                order_num VARCHAR(11) PRIMARY KEY,
     600                client_ID INTEGER REFERENCES client(client_ID) ON DELETE CASCADE,
    527601                status VARCHAR(20) NOT NULL DEFAULT 'placed order',
    528                 last_date_mod TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     602                last_date_mod TIMESTAMP NOT NULL,
    529603                payment_method VARCHAR(250) NOT NULL,
    530                 discount DECIMAL(5,2) DEFAULT 0 CHECK (discount >= 0 AND discount <= 100),
    531                 store_id VARCHAR(3) REFERENCES store(store_id) ON DELETE SET NULL,
    532                 order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    533                 quantity INTEGER DEFAULT 0,
    534                 delivery_address VARCHAR(500),
    535                 CONSTRAINT check_status CHECK (status IN ('placed order','being processed','shipping','delivered','canceled'))
    536                 );
     604                discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<=100.00),
     605                CONSTRAINT check_status CHECK (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled'))
     606            );
     607
    537608            CREATE TABLE IF NOT EXISTS review (
    538                                                   order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num) ON DELETE CASCADE,
     609                order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num) ON DELETE CASCADE,
    539610                comment VARCHAR(300),
    540                 rating DECIMAL(2,1) NOT NULL CHECK (rating >= 0 AND rating <= 5),
    541                 last_mod_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    542                 review_id VARCHAR(20) UNIQUE,
    543                 client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
    544                 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
    545                 review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    546                 );
     611                rating DECIMAL(2,1) NOT NULL CHECK(rating>=0.0 AND rating<=5.0),
     612                last_mod_date TIMESTAMP NOT NULL
     613            );
     614
    547615            CREATE TABLE IF NOT EXISTS refund (
    548                                                   refund_id VARCHAR(50) PRIMARY KEY,
     616                refund_id SERIAL PRIMARY KEY,
    549617                order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE,
    550618                reason VARCHAR(300),
    551                 amount DECIMAL(10,2) NOT NULL,
     619                amount DECIMAL(5,2) NOT NULL,
    552620                status VARCHAR(100) NOT NULL DEFAULT 'requested refund',
    553                 request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    554                 processed_date TIMESTAMP
    555                 );
     621                CONSTRAINT check_refund_status CHECK (status IN ('requested refund', 'being revied', 'approved', 'not approved', 'processed'))
     622            );
     623
    556624            CREATE TABLE IF NOT EXISTS report (
    557                                                   date TIMESTAMP NOT NULL,
    558                                                   store_id VARCHAR(3) NOT NULL REFERENCES store(store_id) ON DELETE CASCADE,
    559                 overall_profit NUMERIC NOT NULL DEFAULT 0 CHECK (overall_profit >= 0),
    560                 sales_trend VARCHAR(100) NOT NULL DEFAULT '',
    561                 marketing_growth VARCHAR(100) NOT NULL DEFAULT '',
     625                date TIMESTAMP NOT NULL,
     626                store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID) ON DELETE CASCADE,
     627                overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0),
     628                sales_trend VARCHAR(100) NOT NULL,
     629                marketing_growth VARCHAR(100) NOT NULL,
    562630                owner_signature VARCHAR(50) NOT NULL DEFAULT 'Not signed yet',
    563                 id VARCHAR(50) UNIQUE,
    564                 period VARCHAR(50),
    565                 start_date DATE,
    566                 end_date DATE,
    567                 type VARCHAR(50),
    568                 generated_by VARCHAR(10) REFERENCES personal(id) ON DELETE SET NULL,
    569                 generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    570                 PRIMARY KEY (date, store_id)
    571                 );
     631                PRIMARY KEY (date, store_ID)
     632            );
     633
    572634            CREATE TABLE IF NOT EXISTS monthly_profit (
    573                                                           report_date TIMESTAMP NOT NULL,
    574                                                           store_id VARCHAR(3) NOT NULL,
     635                report_date TIMESTAMP NOT NULL,
     636                store_ID VARCHAR(3) NOT NULL,
    575637                month_and_year DATE NOT NULL,
    576                 profit NUMERIC NOT NULL DEFAULT 0,
    577                 PRIMARY KEY (report_date, store_id),
    578                 FOREIGN KEY (report_date, store_id) REFERENCES report(date, store_id) ON DELETE CASCADE
    579                 );
     638                profit NUMERIC NOT NULL DEFAULT 0.0,
     639                PRIMARY KEY(report_date, store_ID),
     640                FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE
     641            );
     642
    580643            CREATE TABLE IF NOT EXISTS exchanges_data (
    581                                                           report_date TIMESTAMP NOT NULL,
    582                                                           store_id VARCHAR(3) NOT NULL,
    583                 monthly_profit NUMERIC NOT NULL DEFAULT 0,
     644                report_date TIMESTAMP NOT NULL,
     645                store_ID VARCHAR(3) NOT NULL,
     646                monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
    584647                date TIMESTAMP NOT NULL,
    585                 sales NUMERIC NOT NULL DEFAULT 0,
    586                 damages NUMERIC NOT NULL DEFAULT 0 CHECK (damages <= 0),
    587                 PRIMARY KEY (report_date, store_id),
    588                 FOREIGN KEY (report_date, store_id) REFERENCES report(date, store_id) ON DELETE CASCADE
    589                 );
     648                sales NUMERIC NOT NULL DEFAULT 0.0,
     649                damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages<=0),
     650                PRIMARY KEY (report_date, store_ID),
     651                FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE
     652            );
     653
    590654            CREATE TABLE IF NOT EXISTS request (
    591                                                    request_num VARCHAR(14) PRIMARY KEY,
     655                request_num VARCHAR(14) PRIMARY KEY,
    592656                date_and_time TIMESTAMP NOT NULL,
    593657                problem VARCHAR(300) NOT NULL,
    594658                notes_of_communication VARCHAR,
    595                 customer_satisfaction NUMERIC NOT NULL DEFAULT 0,
    596                 client_id INTEGER REFERENCES client(client_id) ON DELETE CASCADE,
    597                 store_id VARCHAR(3) REFERENCES store(store_id) ON DELETE CASCADE,
    598                 status VARCHAR(50) DEFAULT 'pending'
    599                 );
     659                customer_satisfaction NUMERIC NOT NULL
     660            );
     661
    600662            CREATE TABLE IF NOT EXISTS makes_request (
    601                                                          client_id INTEGER NOT NULL REFERENCES client(client_id) ON DELETE CASCADE,
     663                client_ID INTEGER NOT NULL REFERENCES client(client_ID) ON DELETE CASCADE,
    602664                order_num VARCHAR(11) UNIQUE NOT NULL REFERENCES "order"(order_num) ON DELETE CASCADE,
    603                 PRIMARY KEY(client_id, order_num)
    604                 );
     665                PRIMARY KEY(client_ID, order_num)
     666            );
     667
    605668            CREATE TABLE IF NOT EXISTS answers (
    606                                                    request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE,
     669                request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE,
    607670                personal_id VARCHAR(10) NOT NULL REFERENCES personal(id) ON DELETE CASCADE,
    608671                PRIMARY KEY(request_num, personal_id)
    609                 );
     672            );
     673
    610674            CREATE TABLE IF NOT EXISTS for_store (
    611                                                      request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE,
    612                 store_id VARCHAR(3) REFERENCES store(store_id) ON DELETE CASCADE,
    613                 PRIMARY KEY(request_num, store_id)
    614                 );
     675                request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE,
     676                store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE,
     677                PRIMARY KEY(request_num, store_ID)
     678            );
     679
    615680            CREATE TABLE IF NOT EXISTS "change" (
    616                                                     date_and_time TIMESTAMP NOT NULL,
    617                                                     product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
     681                date_and_time TIMESTAMP NOT NULL,
     682                product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
    618683                changes VARCHAR NOT NULL,
    619                 PRIMARY KEY(date_and_time, product_code)
    620                 );
     684                PRIMARY KEY (date_and_time, product_code)
     685            );
     686
    621687            CREATE TABLE IF NOT EXISTS makes_change (
    622                                                         personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
     688                personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
    623689                change_date_time TIMESTAMP,
    624690                product_code VARCHAR(8),
    625691                PRIMARY KEY(personal_id, change_date_time, product_code),
    626692                FOREIGN KEY(change_date_time, product_code) REFERENCES "change"(date_and_time, product_code) ON DELETE CASCADE
    627                 );
     693            );
     694
    628695            CREATE TABLE IF NOT EXISTS works_in_store (
    629                                                           personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
    630                 store_id VARCHAR(3) REFERENCES store(store_id) ON DELETE CASCADE,
    631                 PRIMARY KEY(personal_id, store_id)
    632                 );
     696                personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
     697                store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE,
     698                PRIMARY KEY(personal_id, store_ID)
     699            );
     700
    633701            CREATE TABLE IF NOT EXISTS worked (
    634                                                   personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
     702                personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
    635703                report_date TIMESTAMP,
    636                 store_id VARCHAR(3),
    637                 wage NUMERIC NOT NULL CHECK(wage >= 0),
    638                 pay_method VARCHAR DEFAULT 'full_time' CHECK(pay_method IN ('full_time','part-time','custom')),
     704                store_ID VARCHAR(3),
     705                wage NUMERIC NOT NULL CHECK (wage>=0),
     706                pay_method VARCHAR DEFAULT 'full-time',
    639707                total_hours NUMERIC NOT NULL,
    640708                week VARCHAR(23) NOT NULL,
    641                 PRIMARY KEY(personal_id, report_date, store_id),
    642                 FOREIGN KEY(report_date, store_id) REFERENCES report(date, store_id) ON DELETE CASCADE
    643                 );
     709                PRIMARY KEY (personal_id, report_date, store_ID),
     710                FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE,
     711                CONSTRAINT check_pay_method CHECK (pay_method IN ('full_time', 'part-time', 'custom'))
     712            );
     713
    644714            CREATE TABLE IF NOT EXISTS sells (
    645                                                  product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
    646                 store_id VARCHAR(3) REFERENCES store(store_id) ON DELETE CASCADE,
    647                 discount NUMERIC NOT NULL DEFAULT 0,
    648                 PRIMARY KEY(product_code, store_id)
    649                 );
     715                product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
     716                store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE,
     717                discount NUMERIC NOT NULL DEFAULT 0.0,
     718                PRIMARY KEY (product_code, store_ID)
     719            );
     720
    650721            CREATE TABLE IF NOT EXISTS includes (
    651                                                     order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE,
     722                order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE,
    652723                product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE,
    653                 quantity INTEGER NOT NULL CHECK(quantity >= 0),
    654                 PRIMARY KEY(order_num, product_code)
    655                 );
     724                quantity INTEGER NOT NULL CHECK(quantity>=0),
     725                PRIMARY KEY (order_num, product_code)
     726            );
     727
    656728            CREATE TABLE IF NOT EXISTS approves (
    657                                                     boss_id VARCHAR(10) REFERENCES boss(boss_id) ON DELETE CASCADE,
     729                boss_id VARCHAR(10) REFERENCES boss(boss_id) ON DELETE CASCADE,
    658730                report_date TIMESTAMP,
    659                 store_id VARCHAR(3),
     731                store_ID VARCHAR(3),
    660732                owner_signature VARCHAR NOT NULL,
    661                 PRIMARY KEY(boss_id, report_date, store_id),
    662                 FOREIGN KEY(report_date, store_id) REFERENCES report(date, store_id) ON DELETE CASCADE
    663                 );
    664 
    665 -- Application support tables required by the existing HTTP/authentication layer.
     733                PRIMARY KEY (boss_id, report_date, store_ID),
     734                FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE
     735            );
     736
     737            -- These four small tables are application authentication/audit storage.
     738            -- They do not modify any of the project tables above.
    666739            CREATE TABLE IF NOT EXISTS users (
    667                                                  id VARCHAR(50) PRIMARY KEY,
     740                id VARCHAR(50) PRIMARY KEY,
    668741                username VARCHAR(100) UNIQUE NOT NULL,
    669742                email VARCHAR(255) UNIQUE NOT NULL,
    … …  
    672745                force_password_change BOOLEAN DEFAULT FALSE,
    673746                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    674                 );
     747            );
     748
    675749            CREATE TABLE IF NOT EXISTS roles (
    676                                                  role_id SERIAL PRIMARY KEY,
    677                                                  name VARCHAR(50) UNIQUE NOT NULL,
     750                role_id SERIAL PRIMARY KEY,
     751                name VARCHAR(50) UNIQUE NOT NULL,
    678752                description TEXT
    679                 );
     753            );
     754
    680755            CREATE TABLE IF NOT EXISTS user_roles (
    681                                                       user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE,
     756                user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE,
    682757                role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE,
    683758                PRIMARY KEY(user_id, role_id)
    684                 );
     759            );
     760
    685761            CREATE TABLE IF NOT EXISTS audit_log (
    686                                                      log_id BIGSERIAL PRIMARY KEY,
    687                                                      user_id VARCHAR(50),
     762                log_id BIGSERIAL PRIMARY KEY,
     763                user_id VARCHAR(50),
    688764                action VARCHAR(100) NOT NULL,
    689765                resource_type VARCHAR(50),
    … …  
    692768                ip_address VARCHAR(45),
    693769                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    694                 );
    695             CREATE INDEX IF NOT EXISTS idx_product_store ON product(store_id);
     770            );
     771
    696772            CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id);
    697             CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_id);
    698             CREATE INDEX IF NOT EXISTS idx_order_store ON "order"(store_id);
    699             CREATE INDEX IF NOT EXISTS idx_order_date ON "order"(order_date);
    700             CREATE INDEX IF NOT EXISTS idx_review_client ON review(client_id);
    701             CREATE INDEX IF NOT EXISTS idx_review_product ON review(product_code);
    702             CREATE INDEX IF NOT EXISTS idx_request_client ON request(client_id);
    703             CREATE INDEX IF NOT EXISTS idx_request_store ON request(store_id);
     773            CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_ID);
     774            CREATE INDEX IF NOT EXISTS idx_review_order ON review(order_num);
     775            CREATE INDEX IF NOT EXISTS idx_request_date ON request(date_and_time);
    704776            CREATE INDEX IF NOT EXISTS idx_refund_order ON refund(order_num);
    705777            CREATE INDEX IF NOT EXISTS idx_personal_email ON personal(email);
    … …  
    708780            CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
    709781            CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id);
    710 
    711782        `;
     783
    712784        await pool.query(schema);
     785
    713786        const roles = [
    714787            ['admin', 'System administrator'],
    … …  
    718791            ['guest', 'Unregistered guest']
    719792        ];
     793
    720794        for (const [name, description] of roles) {
    721795            await pool.query(
    … …  
    725799        }
    726800
    727         const bcrypt = require('bcryptjs');
    728801        const hash = bcrypt.hashSync('Admin123!', 10);
    729802        await pool.query(
    730803            `INSERT INTO users(id, username, email, password, user_type, force_password_change)
    731              VALUES('000000','admin','admin@handcraft.com',$1,'admin',TRUE)
    732                  ON CONFLICT(id) DO NOTHING`,
     804             VALUES('000000','admin','admin@handcraft.com',$1,'admin',TRUE) ON CONFLICT(id) DO NOTHING`,
    733805            [hash]
    734806        );
    735807        await pool.query(
    736808            `INSERT INTO personal(id, first_name, last_name, ssn, email, password)
    737              VALUES('000000','Admin','User','0000000000000','admin@handcraft.com',$1)
    738                  ON CONFLICT(id) DO NOTHING`,
     809             VALUES('000000','Admin','User','0000000000000','admin@handcraft.com',$1) ON CONFLICT(id) DO NOTHING`,
    739810            [hash]
    740811        );
    741         await pool.query(
    742             `INSERT INTO boss(boss_id) VALUES('000000') ON CONFLICT(boss_id) DO NOTHING`
    743         );
     812        await pool.query(`INSERT INTO boss(boss_id) VALUES('000000') ON CONFLICT(boss_id) DO NOTHING`);
    744813        await pool.query(
    745814            `INSERT INTO permissions(personal_is,type,authorisation)
    746              VALUES('000000','ADMIN','full_access')
    747                  ON CONFLICT(personal_is) DO NOTHING`
     815             VALUES('000000','ADMIN','full_access') ON CONFLICT(personal_is) DO NOTHING`
    748816        );
    749817        await pool.query(
    750818            `INSERT INTO user_roles(user_id,role_id)
    751              SELECT '000000', role_id FROM roles WHERE name='admin'
    752                  ON CONFLICT DO NOTHING`
     819             SELECT '000000', role_id FROM roles WHERE name='admin' ON CONFLICT DO NOTHING`
    753820        );
    754         await pool.query(
    755             `INSERT INTO category(name,parent_category_id)
    756              SELECT 'General', NULL
    757                  WHERE NOT EXISTS (SELECT 1 FROM category WHERE name='General')`
    758         );
    759         console.log('✅ PostgreSQL schema is ready');
     821        console.log('✅ PostgreSQL project schema was recreated successfully');
    760822    },
    761823
    … …  
    770832                             FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles
    771833             FROM users u
    772                       LEFT JOIN user_roles ur ON ur.user_id=u.id
    773                       LEFT JOIN roles r ON r.role_id=ur.role_id
     834             LEFT JOIN user_roles ur ON ur.user_id=u.id
     835             LEFT JOIN roles r ON r.role_id=ur.role_id
    774836             WHERE u.id=$1
    775837             GROUP BY u.id`,
    … …  
    785847                             FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles
    786848             FROM users u
    787                       LEFT JOIN user_roles ur ON ur.user_id=u.id
    788                       LEFT JOIN roles r ON r.role_id=ur.role_id
     849             LEFT JOIN user_roles ur ON ur.user_id=u.id
     850             LEFT JOIN roles r ON r.role_id=ur.role_id
    789851             WHERE u.username=$1 OR u.email=$1
    790852             GROUP BY u.id
    791                  LIMIT 1`,
     853             LIMIT 1`,
    792854            [username],
    793855            (err, result) => callback(err, result?.rows?.[0])
    … …  
    807869                dbQuery(
    808870                    `INSERT INTO user_roles(user_id,role_id)
    809                      SELECT $1, role_id FROM roles WHERE name=$2
    810                          ON CONFLICT DO NOTHING`,
     871                     SELECT $1, role_id FROM roles WHERE name=$2`,
    811872                    [String(id), roleName],
    812873                    roleErr => callback(roleErr, String(id))
    … …  
    820881            `INSERT INTO client(first_name,last_name,email,password)
    821882             VALUES($1,$2,$3,$4) RETURNING client_id`,
    822             [data.firstName || data.first_name, data.lastName || data.last_name, data.email, data.password],
     883            [data.firstName || data.first_name || '', data.lastName || data.last_name || '', data.email, data.password],
    823884            (err, result) => callback(err, result?.rows?.[0]?.client_id)
    824885        );
    … …  
    846907
    847908    verifyPassword(password, hash) {
    848         const bcrypt = require('bcryptjs');
    849909        try { return bcrypt.compareSync(password, hash); } catch { return false; }
    850910    },
    851911
    852912    verifyClientPassword(password, hash, callback) {
    853         require('bcryptjs').compare(password, hash, callback);
     913        bcrypt.compare(password, hash, callback);
    854914    },
    855915
    … …  
    858918            `INSERT INTO audit_log(user_id,action,resource_type,resource_id,details,ip_address)
    859919             VALUES($1,$2,$3,$4,$5,$6)`,
    860             [userId == null ? null : String(userId), action, resourceType, resourceId == null ? null : String(resourceId), details, ipAddress],
     920            [userId == null ? null : String(userId), action, resourceType,
     921                resourceId == null ? null : String(resourceId), details, ipAddress],
    861922            () => {}
    862923        );
    863924    },
    864925
    865     ensureGeneralCategory(callback) {
    866         dbQuery(
    867             `INSERT INTO category(name,parent_category_id)
    868              SELECT 'General',NULL
    869                  WHERE NOT EXISTS(SELECT 1 FROM category WHERE name='General')`,
    870             [],
    871             err => callback(err)
    872         );
    873     },
    874 
    875926    getProducts(categoryId, searchTerm, callback) {
    876927        const params = [];
    877928        const where = [];
    878         if (categoryId) { params.push(categoryId); where.push(`p.category_id=$${params.length}`); }
     929        if (categoryId) {
     930            params.push(categoryId);
     931            where.push(`p.category_id=$${params.length}`);
     932        }
    879933        if (searchTerm) {
    880934            params.push(`%${searchTerm}%`);
    881935            where.push(`(p.description ILIKE $${params.length} OR p.code ILIKE $${params.length})`);
    882936        }
    883         const sql = `SELECT p.*, c.name AS category_name, p.store_id
    884                      FROM product p LEFT JOIN category c ON c.id=p.category_id
    885                          ${where.length ? 'WHERE '+where.join(' AND ') : ''}
     937        const sql = `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id
     938                     FROM product p
     939                     LEFT JOIN category c ON c.id=p.category_id
     940                     ${where.length ? 'WHERE ' + where.join(' AND ') : ''}
    886941                     ORDER BY p.code`;
    887942        dbQuery(sql, params, (err, result) => callback(err, result?.rows || []));
    … …  
    890945    getProductById(id, callback) {
    891946        dbQuery(
    892             `SELECT p.*,c.name AS category_name FROM product p
    893                                                          LEFT JOIN category c ON c.id=p.category_id WHERE p.code=$1 OR p.code::text=$1 LIMIT 1`,
     947            `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id
     948             FROM product p
     949             LEFT JOIN category c ON c.id=p.category_id
     950             WHERE p.code=$1 LIMIT 1`,
    894951            [String(id)],
    895952            (err,result)=>callback(err,result?.rows?.[0])
    … …  
    899956    getProductByCode(code, callback) {
    900957        dbQuery(
    901             `SELECT p.*,c.name AS category_name FROM product p
    902                                                          LEFT JOIN category c ON c.id=p.category_id WHERE p.code=$1`,
     958            `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id
     959             FROM product p
     960             LEFT JOIN category c ON c.id=p.category_id
     961             WHERE p.code=$1`,
    903962            [code],
    904963            (err,result)=>callback(err,result?.rows?.[0])
    … …  
    907966
    908967    addProduct(personalId, data, callback) {
    909         const storeId = data.store_id || data.storeId || String(data.code).slice(0,3);
     968        const storeId = data.store_id || data.storeId || String(data.code || '').slice(0,3);
     969        if (!data.category_id) {
     970            return callback(new Error('category_id is required because product.category_id is NOT NULL'));
     971        }
    910972        dbQuery(
    911973            `INSERT INTO product(code,price,availability,weight,width_x_length_x_depth,
    912                                  aprox_production_time,description,category_id,store_id)
    913              VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9) RETURNING code`,
     974                                 aprox_production_time,description,category_id)
     975             VALUES($1,$2,$3,$4,$5,$6,$7,$8) RETURNING code`,
    914976            [
    915977                data.code, data.price, data.availability ?? 0, data.weight,
    916978                data.width_x_length_x_depth || data.dimensions || '',
    917                 data.aprox_production_time || data.production_time || 0,
    918                 data.description, data.category_id, storeId
     979                data.aprox_production_time ?? data.production_time ?? 0,
     980                data.description, data.category_id
    919981            ],
    920982            (err,result)=>{
    921983                if (err) return callback(err);
    922984                dbQuery(
    923                     `INSERT INTO sells(product_code,store_id,discount)
    924                      VALUES($1,$2,$3) ON CONFLICT(product_code,store_id) DO UPDATE SET discount=EXCLUDED.discount`,
     985                    `INSERT INTO sells(product_code,store_ID,discount)
     986                     VALUES($1,$2,$3)
     987                     ON CONFLICT(product_code,store_ID)
     988                     DO UPDATE SET discount=EXCLUDED.discount`,
    925989                    [data.code,storeId,data.discount || 0],
    926990                    e => callback(e, data.code)
    … …  
    934998        const params = [];
    935999        const allowed = [
    936             ['price','price'],['availability','availability'],['weight','weight'],
     1000            ['price','price'], ['availability','availability'], ['weight','weight'],
    9371001            ['width_x_length_x_depth','width_x_length_x_depth'],
    9381002            ['dimensions','width_x_length_x_depth'],
    9391003            ['aprox_production_time','aprox_production_time'],
    9401004            ['production_time','aprox_production_time'],
    941             ['description','description'],['category_id','category_id']
     1005            ['description','description'], ['category_id','category_id']
    9421006        ];
    9431007        for (const [input,col] of allowed) {
    … …  
    9541018
    9551019    deleteProduct(productCode, storeId, personalId, callback) {
    956         dbQuery('DELETE FROM product WHERE code=$1 AND store_id=$2', [productCode,storeId],
     1020        dbQuery('DELETE FROM product WHERE code=$1 AND LEFT(code,3)=$2', [productCode,storeId],
    9571021            (err)=>callback(err));
    9581022    },
    9591023
    9601024    createCategory(data, callback) {
     1025        const parent = data.parent_category_id ?? data.parentCategoryId ?? data.parent_id;
     1026        if (parent === undefined || parent === null || parent === '') {
     1027            return callback(new Error('parent_category_id is required by the project schema'));
     1028        }
    9611029        dbQuery(
    9621030            `INSERT INTO category(name,parent_category_id) VALUES($1,$2) RETURNING id,name,parent_category_id`,
    963             [data.name, data.parent_category_id || null],
     1031            [data.name, parent],
    9641032            (err,result)=>callback(err,result?.rows?.[0])
    9651033        );
    … …  
    9861054        const items = data.items || data.products || data.order_items || [];
    9871055        const storeId = data.store_id || data.storeId || (items[0]?.code ? String(items[0].code).slice(0,3) : null);
    988         const now = new Date();
    989         const year = String(now.getFullYear()).slice(-3);
    990         const prefix = storeId || '000';
    991         const insertOrder = () => {
    992             dbQuery(
    993                 `SELECT COUNT(*)::int AS n FROM "order" WHERE store_id=$1 AND EXTRACT(YEAR FROM order_date)=EXTRACT(YEAR FROM CURRENT_DATE)`,
    994                 [storeId],
    995                 (countErr,countResult)=>{
    996                     if (countErr) return callback(countErr);
    997                     const seq=Number(countResult.rows[0].n)+1;
    998                     const orderNum=`${prefix}${year}${String(seq).padStart(5,'0')}`;
    999                     dbQuery(
    1000                         `INSERT INTO "order"(order_num,client_id,status,last_date_mod,payment_method,discount,store_id,order_date,quantity,delivery_address)
    1001                          VALUES($1,$2,$3,CURRENT_TIMESTAMP,$4,$5,$6,CURRENT_TIMESTAMP,$7,$8) RETURNING order_num`,
    1002                         [orderNum,data.client_id,data.status||'placed order',data.payment_method,data.discount||0,storeId,
    1003                             items.reduce((n,x)=>n+Number(x.quantity||1),0),data.delivery_address||data.deliveryAddress||null],
    1004                         (err,result)=>{
    1005                             if(err) return callback(err);
    1006                             let pending=items.length;
    1007                             if(!pending) return callback(null,orderNum);
    1008                             let firstErr=null;
    1009                             for(const item of items){
    1010                                 dbQuery(
    1011                                     `INSERT INTO includes(order_num,product_code,quantity) VALUES($1,$2,$3)`,
    1012                                     [orderNum,item.product_code||item.code,item.quantity||1],
    1013                                     e=>{ if(e) firstErr ||= e; if(--pending===0) callback(firstErr,firstErr?undefined:orderNum); }
    1014                                 );
    1015                             }
     1056        if (!storeId) return callback(new Error('Store ID is required'));
     1057        const year = String(new Date().getFullYear()).slice(-3);
     1058
     1059        dbQuery(
     1060            `SELECT COUNT(*)::int AS n
     1061             FROM "order"
     1062             WHERE LEFT(order_num,3)=$1
     1063               AND SUBSTRING(order_num FROM 4 FOR 3)=$2`,
     1064            [storeId, year],
     1065            (countErr,countResult)=>{
     1066                if (countErr) return callback(countErr);
     1067                const seq=Number(countResult.rows[0].n)+1;
     1068                const orderNum=`${storeId}${year}${String(seq).padStart(5,'0')}`;
     1069                dbQuery(
     1070                    `INSERT INTO "order"(order_num,client_ID,status,last_date_mod,payment_method,discount)
     1071                     VALUES($1,$2,$3,CURRENT_TIMESTAMP,$4,$5) RETURNING order_num`,
     1072                    [orderNum,data.client_id,data.status||'placed order',data.payment_method||'cash',data.discount||0],
     1073                    (err,result)=>{
     1074                        if(err) return callback(err);
     1075                        let pending=items.length;
     1076                        if(!pending) return callback(null,orderNum);
     1077                        let firstErr=null;
     1078                        for(const item of items){
     1079                            dbQuery(
     1080                                `INSERT INTO includes(order_num,product_code,quantity) VALUES($1,$2,$3)`,
     1081                                [orderNum,item.product_code||item.code,item.quantity||1],
     1082                                e=>{ if(e && !firstErr) firstErr=e; if(--pending===0) callback(firstErr,firstErr?undefined:orderNum); }
     1083                            );
    10161084                        }
    1017                     );
    1018                 }
    1019             );
    1020         };
    1021         insertOrder();
     1085                    }
     1086                );
     1087            }
     1088        );
    10221089    },
    10231090
    10241091    getOrdersByClient(clientId, callback) {
    10251092        dbQuery(
    1026             `SELECT o.*,COALESCE(json_agg(json_build_object('product_code',i.product_code,'quantity',i.quantity,'price',p.price))
    1027                                  FILTER(WHERE i.product_code IS NOT NULL),'[]') AS items
     1093            `SELECT o.*, LEFT(o.order_num,3) AS store_id,
     1094                    o.last_date_mod AS order_date,
     1095                    COALESCE(json_agg(json_build_object('product_code',i.product_code,'quantity',i.quantity,'price',p.price))
     1096                             FILTER(WHERE i.product_code IS NOT NULL),'[]') AS items
    10281097             FROM "order" o
    1029                       LEFT JOIN includes i ON i.order_num=o.order_num
    1030                       LEFT JOIN product p ON p.code=i.product_code
    1031              WHERE o.client_id=$1 GROUP BY o.order_num ORDER BY o.order_date DESC`,
     1098             LEFT JOIN includes i ON i.order_num=o.order_num
     1099             LEFT JOIN product p ON p.code=i.product_code
     1100             WHERE o.client_ID=$1
     1101             GROUP BY o.order_num
     1102             ORDER BY o.last_date_mod DESC`,
    10321103            [clientId],(err,result)=>callback(err,result?.rows||[])
    10331104        );
    … …  
    10351106
    10361107    createReviewNew(data, callback) {
    1037         const reviewId = data.review_id || ('REV'+Date.now());
    10381108        dbQuery(
    1039             `INSERT INTO review(order_num,comment,rating,last_mod_date,review_id,client_id,product_code,review_date)
    1040              VALUES($1,$2,$3,CURRENT_TIMESTAMP,$4,$5,$6,CURRENT_TIMESTAMP)
    1041                  RETURNING review_id`,
    1042             [data.order_num,data.comment||null,data.rating,reviewId,data.client_id||null,data.product_code||null],
    1043             (err,result)=>callback(err,result?.rows?.[0]?.review_id || reviewId)
     1109            `INSERT INTO review(order_num,comment,rating,last_mod_date)
     1110             VALUES($1,$2,$3,CURRENT_TIMESTAMP)
     1111             RETURNING order_num`,
     1112            [data.order_num,data.comment||null,data.rating],
     1113            (err,result)=>callback(err,result?.rows?.[0]?.order_num)
    10441114        );
    10451115    },
    … …  
    10471117    createRequest(data, callback) {
    10481118        dbQuery(
    1049             `INSERT INTO request(request_num,date_and_time,problem,notes_of_communication,customer_satisfaction,client_id,store_id)
    1050              VALUES($1,$2,$3,$4,0,$5,$6) RETURNING request_num`,
    1051             [data.request_num,data.date_and_time,data.problem,data.notes_of_communication||null,data.client_id,data.store_id],
    1052             (err,result)=>callback(err,result?.rows?.[0]?.request_num)
     1119            `INSERT INTO request(request_num,date_and_time,problem,notes_of_communication,customer_satisfaction)
     1120             VALUES($1,$2,$3,$4,0) RETURNING request_num`,
     1121            [data.request_num,data.date_and_time,data.problem,data.notes_of_communication||null],
     1122            (err,result)=>{
     1123                if (err) return callback(err);
     1124                dbQuery(
     1125                    `INSERT INTO for_store(request_num,store_ID) VALUES($1,$2)`,
     1126                    [data.request_num,data.store_id],
     1127                    storeErr=>{
     1128                        if (storeErr) return callback(storeErr);
     1129                        if (data.order_num) {
     1130                            dbQuery(
     1131                                `INSERT INTO makes_request(client_ID,order_num) VALUES($1,$2)`,
     1132                                [data.client_id,data.order_num],
     1133                                e=>callback(e,data.request_num)
     1134                            );
     1135                        } else {
     1136                            callback(null,data.request_num);
     1137                        }
     1138                    }
     1139                );
     1140            }
    10531141        );
    10541142    },
    10551143
    10561144    createRefund(data, callback) {
    1057         dbQuery(
    1058             `INSERT INTO refund(refund_id,order_num,reason,amount,status,request_date)
    1059              VALUES($1,$2,$3,$4,$5,CURRENT_TIMESTAMP) RETURNING refund_id`,
    1060             [String(data.refund_id),data.order_num,data.reason||null,data.amount,data.status||'requested refund'],
    1061             (err,result)=>callback(err,result?.rows?.[0]?.refund_id)
    1062         );
     1145        const suppliedId = data.refund_id;
     1146        const query = suppliedId
     1147            ? `INSERT INTO refund(refund_id,order_num,reason,amount,status) VALUES($1,$2,$3,$4,$5) RETURNING refund_id`
     1148            : `INSERT INTO refund(order_num,reason,amount,status) VALUES($1,$2,$3,$4) RETURNING refund_id`;
     1149        const params = suppliedId
     1150            ? [Number.parseInt(String(suppliedId),10) || undefined,data.order_num,data.reason||null,data.amount,data.status||'requested refund']
     1151            : [data.order_num,data.reason||null,data.amount,data.status||'requested refund'];
     1152        dbQuery(query,params,(err,result)=>callback(err,result?.rows?.[0]?.refund_id));
    10631153    },
    10641154
    … …  
    10711161
    10721162    getAllOrders(callback) {
    1073         dbQuery(`SELECT o.*,c.first_name,c.last_name,c.email FROM "order" o
    1074                                                                       LEFT JOIN client c ON c.client_id=o.client_id ORDER BY o.order_date DESC`,
     1163        dbQuery(`SELECT o.*, LEFT(o.order_num,3) AS store_id, o.last_date_mod AS order_date,
     1164                        c.first_name,c.last_name,c.email
     1165                 FROM "order" o
     1166                 LEFT JOIN client c ON c.client_id=o.client_ID
     1167                 ORDER BY o.last_date_mod DESC`,
    10751168            [],(err,result)=>callback(err,result?.rows||[]));
    10761169    },
    10771170
    10781171    getStoreProducts(storeId, callback) {
    1079         dbQuery(`SELECT p.*,c.name AS category_name,s.discount FROM product p
    1080                                                                         LEFT JOIN category c ON c.id=p.category_id
    1081                                                                         LEFT JOIN sells s ON s.product_code=p.code AND s.store_id=$1
    1082                  WHERE p.store_id=$1 OR EXISTS(SELECT 1 FROM sells sx WHERE sx.product_code=p.code AND sx.store_id=$1)
     1172        dbQuery(`SELECT p.*,c.name AS category_name,LEFT(p.code,3) AS store_id,s.discount
     1173                 FROM product p
     1174                 LEFT JOIN category c ON c.id=p.category_id
     1175                 LEFT JOIN sells s ON s.product_code=p.code AND s.store_ID=$1
     1176                 WHERE LEFT(p.code,3)=$1 OR EXISTS(SELECT 1 FROM sells sx WHERE sx.product_code=p.code AND sx.store_ID=$1)
    10831177                 ORDER BY p.code`,[storeId],(err,result)=>callback(err,result?.rows||[]));
    10841178    },
    10851179
    10861180    getStoreOrders(storeId, callback) {
    1087         dbQuery(`SELECT o.*,c.first_name,c.last_name FROM "order" o
    1088                                                               LEFT JOIN client c ON c.client_id=o.client_id
    1089                  WHERE o.store_id=$1 ORDER BY o.order_date DESC`,[storeId],
     1181        dbQuery(`SELECT o.*,LEFT(o.order_num,3) AS store_id,o.last_date_mod AS order_date,c.first_name,c.last_name
     1182                 FROM "order" o
     1183                 LEFT JOIN client c ON c.client_id=o.client_ID
     1184                 WHERE LEFT(o.order_num,3)=$1 ORDER BY o.last_date_mod DESC`,[storeId],
    10901185            (err,result)=>callback(err,result?.rows||[]));
    10911186    },
    … …  
    10941189        dbQuery(`SELECT p.*,e.date_of_hire,per.type,per.authorisation
    10951190                 FROM personal p JOIN works_in_store w ON w.personal_id=p.id
    1096                                  LEFT JOIN employees e ON e.employee_id=p.id
    1097                                  LEFT JOIN permissions per ON per.personal_is=p.id
    1098                  WHERE w.store_id=$1 ORDER BY p.last_name,p.first_name`,
     1191                 LEFT JOIN employees e ON e.employee_id=p.id
     1192                 LEFT JOIN permissions per ON per.personal_is=p.id
     1193                 WHERE w.store_ID=$1 ORDER BY p.last_name,p.first_name`,
    10991194            [storeId],(err,result)=>callback(err,result?.rows||[]));
    11001195    },
    11011196
    11021197    getStoreReports(storeId, callback) {
    1103         dbQuery(`SELECT * FROM report WHERE store_id=$1 ORDER BY date DESC`,[storeId],
     1198        dbQuery(`SELECT * FROM report WHERE store_ID=$1 ORDER BY date DESC`,[storeId],
    11041199            (err,result)=>callback(err,result?.rows||[]));
    11051200    },
    … …  
    11071202    getStoreStats(storeId, callback) {
    11081203        const sql=`SELECT
    1109                            (SELECT COUNT(*) FROM product WHERE store_id=$1)::int AS product_count,
    1110                            (SELECT COUNT(*) FROM "order" WHERE store_id=$1)::int AS order_count,
    1111                            (SELECT COALESCE(SUM(i.quantity*p.price*(1-o.discount/100)),0)
    1112                             FROM "order" o JOIN includes i ON i.order_num=o.order_num JOIN product p ON p.code=i.product_code
    1113                             WHERE o.store_id=$1) AS revenue,
    1114                            (SELECT COUNT(*) FROM works_in_store WHERE store_id=$1)::int AS employee_count,
    1115                            (SELECT COUNT(*) FROM request WHERE store_id=$1)::int AS request_count,
    1116                            (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE o.store_id=$1)::int AS refund_count`;
     1204            (SELECT COUNT(*) FROM product WHERE LEFT(code,3)=$1)::int AS product_count,
     1205            (SELECT COUNT(*) FROM "order" WHERE LEFT(order_num,3)=$1)::int AS order_count,
     1206            (SELECT COALESCE(SUM(i.quantity*p.price*(1-o.discount/100)),0)
     1207             FROM "order" o JOIN includes i ON i.order_num=o.order_num JOIN product p ON p.code=i.product_code
     1208             WHERE LEFT(o.order_num,3)=$1) AS revenue,
     1209            (SELECT COUNT(*) FROM works_in_store WHERE store_ID=$1)::int AS employee_count,
     1210            (SELECT COUNT(*) FROM for_store WHERE store_ID=$1)::int AS request_count,
     1211            (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE LEFT(o.order_num,3)=$1)::int AS refund_count`;
    11171212        dbQuery(sql,[storeId],(err,result)=>callback(err,result?.rows?.[0]||{}));
    11181213    },
    … …  
    11211216        dbQuery(`SELECT r.*,a.personal_id AS answered_by
    11221217                 FROM request r
    1123                           LEFT JOIN answers a ON a.request_num=r.request_num
    1124                  WHERE r.store_id=$1 AND (a.personal_id=$2 OR a.personal_id IS NULL)
     1218                 JOIN for_store fs ON fs.request_num=r.request_num
     1219                 LEFT JOIN answers a ON a.request_num=r.request_num
     1220                 WHERE fs.store_ID=$1 AND (a.personal_id=$2 OR a.personal_id IS NULL)
    11251221                 ORDER BY r.date_and_time DESC`,
    11261222            [storeId,personalId],(err,result)=>callback(err,result?.rows||[]));
    … …  
    11291225    getClientStats(clientId, callback) {
    11301226        dbQuery(`SELECT
    1131                          (SELECT COUNT(*) FROM "order" WHERE client_id=$1)::int AS order_count,
    1132                          (SELECT COUNT(*) FROM review WHERE client_id=$1)::int AS review_count,
    1133                          (SELECT COUNT(*) FROM request WHERE client_id=$1)::int AS request_count,
    1134                          (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_id=$1)::int AS refund_count`,
     1227            (SELECT COUNT(*) FROM "order" WHERE client_ID=$1)::int AS order_count,
     1228            (SELECT COUNT(*) FROM review r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS review_count,
     1229            (SELECT COUNT(*) FROM makes_request WHERE client_ID=$1)::int AS request_count,
     1230            (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS refund_count`,
    11351231            [clientId],(err,result)=>callback(err,result?.rows?.[0]||{}));
    11361232    }
    11371233};
    1138 
    11391234
    11401235
    … …  
    11441239// declarations in the original paste are corrected here (for example DECIMMAL,
    11451240// PARTIAL KEY, and composite-key column-name typos). The existing HTTP layer also
    1146 // needs the small application-support tables and compatibility columns defined below.
     1241// uses only the project schema plus the four authentication/audit support tables.
    11471242(async () => {
    11481243    try {
    … …  
    18761971                                    // Insert into boss table (boss_id is VARCHAR, references personal.id)
    18771972                                    database.database.run(
    1878                                         'INSERT INTO boss (boss_id, signature) VALUES ($1, $2)',
    1879                                         [tempStoreData.personalId, tempStoreData.signature],
     1973                                        'INSERT INTO boss (boss_id) VALUES ($1)',
     1974                                        [tempStoreData.personalId],
    18801975                                        (err) => {
    18811976                                            if (err) {
    … …  
    30113106                                id: category.id,
    30123107                                name: category.name,
    3013                                 parent_id: category.parent_id,
     3108                                parent_id: category.parent_category_id,
    30143109                                description: category.description
    30153110                            }
    … …  
    30773172
    30783173                    database.database.get(
    3079                         'SELECT COUNT(*) AS order_count FROM "order" WHERE store_id = $1 AND EXTRACT(YEAR FROM order_date)::INTEGER = $2::INTEGER',
     3174                        'SELECT COUNT(*) AS order_count FROM "order" WHERE LEFT(order_num,3) = $1 AND SUBSTRING(order_num FROM 4 FOR 3)::INTEGER = ($2::INTEGER % 1000)',
    30803175                        [storeId, new Date().getFullYear().toString()],
    30813176                        (err, result) => {
    … …  
    32083303
    32093304                    database.database.get(
    3210                         'SELECT COUNT(*)::int AS request_count FROM request WHERE store_id = $1 AND EXTRACT(YEAR FROM date_and_time)::int = $2::int AND EXTRACT(MONTH FROM date_and_time)::int = $3::int',
     3305                        'SELECT COUNT(*)::int AS request_count FROM request r JOIN for_store fs ON fs.request_num=r.request_num WHERE fs.store_ID = $1 AND EXTRACT(YEAR FROM r.date_and_time)::int = $2::int AND EXTRACT(MONTH FROM r.date_and_time)::int = $3::int',
    32113306                        [storeId, now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')],
    32123307                        (err, result) => {
    … …  
    32663361
    32673362                    database.database.get(
    3268                         'SELECT store_id FROM "order" WHERE order_num = $1',
     3363                        'SELECT LEFT(order_num,3) AS store_id FROM "order" WHERE order_num = $1',
    32693364                        [refundData.order_num],
    32703365                        (err, result) => {
    … …  
    32813376
    32823377                            database.database.get(
    3283                                 'SELECT COUNT(*)::int AS refund_count FROM refund WHERE EXTRACT(YEAR FROM request_date)::int = $1::int AND EXTRACT(MONTH FROM request_date)::int = $2::int',
     3378                                'SELECT COUNT(*)::int AS refund_count FROM refund WHERE SUBSTRING(refund_id::text FROM 4 FOR 2)::int = $2::int AND SUBSTRING(refund_id::text FROM 6 FOR 3)::int = ($1::int % 1000)',
    32843379                                [now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')],
    32853380                                (err, result) => {
    … …  
    33603455                                // PostgreSQL: extract the numeric product sequence from the store-prefixed product code
    33613456                                database.database.get(
    3362                                     'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) AS max_product_num FROM product WHERE store_id = $1',
     3457                                    'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) AS max_product_num FROM product WHERE LEFT(code,3) = $1',
    33633458                                    [storeId],
    33643459                                    (err, result) => {
    … …  
    34293524
    34303525                database.database.get(
    3431                     'SELECT store_id FROM product WHERE code = $1',
     3526                    'SELECT LEFT(code,3) AS store_id FROM product WHERE code = $1',
    34323527                    [productData.code],
    34333528                    (err, product) => {
    … …  
    48324927
    48334928                        database.database.run(
    4834                             'INSERT INTO report (id, store_id, period, start_date, end_date, type, generated_by, generated_at) VALUES ($1, $2, $3, $4, $5, $6, $7, CURRENT_TIMESTAMP)',
    4835                             [reportId, storeId, period, startDate, endDate, type, personalId],
     4929                            'INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES (CURRENT_TIMESTAMP, $1, 0, $2, $3, $4)',
     4930                            [storeId, period, type, 'Not signed yet'],
    48364931                            function(err) {
    48374932                                if (err) {
Note: See TracChangeset for help on using the changeset viewer.